Execute the following T-SQL scripts in Microsoft SQL Server Management Studio (SSMS) Query Editor to demonstrate T-SQL CONVERT and CAST functions in transforming string SQL date formats, string time & string datetime data to datetime data type. Practical examples for T-SQL DATE / DATETIME functions.
— SQL Server string to date / datetime conversion – datetime string format sql server
— MSSQL string to datetime conversion – convert char to date – convert varchar to date
— Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
SELECT convert(datetime, ‘Oct 23 2012 11:01AM’, 100) — mon dd yyyy hh:mmAM (or PM)
SELECT convert(datetime, ‘Oct 23 2012 11:01AM’) — 2012-10-23 11:01:00.000
— Without century (yy) string date conversion – convert string to datetime function
SELECT convert(datetime, ‘Oct 23 12 11:01AM’, 0) — mon dd yy hh:mmAM (or PM)
SELECT convert(datetime, ‘Oct 23 12 11:01AM’) — 2012-10-23 11:01:00.000
— Convert string to datetime sql – convert string to date sql – sql dates format
— T-SQL convert string to datetime – SQL Server convert string to date
SELECT convert(datetime, ’10/23/2016′, 101) — mm/dd/yyyy
SELECT convert(datetime, ‘2016.10.23’, 102) — yyyy.mm.dd ANSI date with century
SELECT convert(datetime, ’23/10/2016′, 103) — dd/mm/yyyy
SELECT convert(datetime, ‘23.10.2016’, 104) — dd.mm.yyyy
SELECT convert(datetime, ’23-10-2016′, 105) — dd-mm-yyyy
— mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, ’23 OCT 2016′, 106) — dd mon yyyy
SELECT convert(datetime, ‘Oct 23, 2016′, 107) — mon dd, yyyy
— 2016-10-23 00:00:00.000
SELECT convert(datetime, ’20:10:44’, 108) — hh:mm:ss
— 1900-01-01 20:10:44.000
— mon dd yyyy hh:mm:ss:mmmAM (or PM) – sql time format – SQL Server datetime format
SELECT convert(datetime, ‘Oct 23 2016 11:02:44:013AM’, 109)
— 2016-10-23 11:02:44.013
SELECT convert(datetime, ’10-23-2016′, 110) — mm-dd-yyyy
SELECT convert(datetime, ‘2016/10/23’, 111) — yyyy/mm/dd
— YYYYMMDD ISO date format works at any language setting – international standard
SELECT convert(datetime, ‘20161023’)
SELECT convert(datetime, ‘20161023’, 112) — ISO yyyymmdd
— 2016-10-23 00:00:00.000
SELECT convert(datetime, ’23 Oct 2016 11:02:07:577′, 113) — dd mon yyyy hh:mm:ss:mmm
— 2016-10-23 11:02:07.577
SELECT convert(datetime, ’20:10:25:300′, 114) — hh:mm:ss:mmm(24h)
— 1900-01-01 20:10:25.300
SELECT convert(datetime, ‘2016-10-23 20:44:11’, 120) — yyyy-mm-dd hh:mm:ss(24h)
— 2016-10-23 20:44:11.000
SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 121) — yyyy-mm-dd hh:mm:ss.mmm
— 2016-10-23 20:44:11.500
— Style 126 is ISO 8601 format: international standard – works with any language setting
SELECT convert(datetime, ‘2008-10-23T18:52:47.513′, 126) — yyyy-mm-ddThh:mm:ss(.mmm)
— 2008-10-23 18:52:47.513
SELECT convert(datetime, N’23 شوال 1429 6:52:47:513PM’, 130) — Islamic/Hijri date
SELECT convert(datetime, ’23/10/1429 6:52:47:513PM’, 131) — Islamic/Hijri date
— Convert DDMMYYYY format to datetime – sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF(‘31012016′,3,0,’-‘),6,0,’-‘), 105)
— 2016-01-31 00:00:00.000
— SQL Server T-SQL string to datetime conversion without century – some exceptions
— nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT convert(datetime, ‘Oct 23 16 11:02:44AM’) — Default
SELECT convert(datetime, ’10/23/16′, 1) — mm/dd/yy U.S.
SELECT convert(datetime, ‘16.10.23’, 2) — yy.mm.dd ANSI
SELECT convert(datetime, ’23/10/16′, 3) — dd/mm/yy UK/FR
SELECT convert(datetime, ‘23.10.16’, 4) — dd.mm.yy German
SELECT convert(datetime, ’23-10-16′, 5) — dd-mm-yy Italian
SELECT convert(datetime, ’23 OCT 16′, 6) — dd mon yy non-det.
SELECT convert(datetime, ‘Oct 23, 16′, 7) — mon dd, yy non-det.
SELECT convert(datetime, ’20:10:44’, 8) — hh:mm:ss
SELECT convert(datetime, ‘Oct 23 16 11:02:44:013AM’, 9) — Default with msec
SELECT convert(datetime, ’10-23-16′, 10) — mm-dd-yy U.S.
SELECT convert(datetime, ’16/10/23′, 11) — yy/mm/dd Japan
SELECT convert(datetime, ‘161023’, 12) — yymmdd ISO
SELECT convert(datetime, ’23 Oct 16 11:02:07:577′, 13) — dd mon yy hh:mm:ss:mmm EU dflt
SELECT convert(datetime, ’20:10:25:300′, 14) — hh:mm:ss:mmm(24h)
SELECT convert(datetime, ‘2016-10-23 20:44:11’,20) — yyyy-mm-dd hh:mm:ss(24h) ODBC can.
SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 21)– yyyy-mm-dd hh:mm:ss.mmm ODBC
————
— SQL Datetime Data Type: Combine date & time string into datetime – sql hh mm ss
— String to datetime – mssql datetime – sql convert date – sql concatenate string
DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)
SELECT @DateValue = ‘20120718’,
@TimeValue = ‘211920’
SELECT @DateTimeValue =
convert(varchar, convert(datetime, @DateValue), 111)
+ ‘ ‘ + substring(@TimeValue, 1, 2)
+ ‘:’ + substring(@TimeValue, 3, 2)
+ ‘:’ + substring(@TimeValue, 5, 2)
SELECT
DateInput = @DateValue, TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue;
/*
DateInput TimeInput DateTimeOutput
20120718 211920 2012/07/18 21:19:20 */
/* DATETIME 8 bytes internal storage structure
o 1st 4 bytes: number of days after the base date 1900-01-01
o 2nd 4 bytes: number of clock-ticks (3.33 milliseconds) since midnight
DATETIME2 8 bytes (precision > 4) internal storage structure
o 1st byte: precision like 7
o middle 4 bytes: number of time units (100ns smallest) since midnight
o last 3 bytes: number of days after the base date 0001-01-01
DATE 3 bytes internal storage structure
o 3 bytes integer: number of days after the first date 0001-01-01
o Note: hex byte order reversed
SMALLDATETIME 4 bytes internal storage structure
o 1st 2 bytes: number of days after the base date 1900-01-01
o 2nd 2 bytes: number of minutes since midnight */
SELECT CONVERT(binary(8), getdate()) — 0x00009E4D 00C01272
SELECT CONVERT(binary(4), convert(smalldatetime,getdate())) — 0x9E4D 02BC
— This is how a datetime looks in 8 bytes
DECLARE @dtHex binary(8)= 0x00009966002d3344;
DECLARE @dt datetime = @dtHex
SELECT @dt — 2007-07-09 02:44:34.147
———— */
————
— SQL Server 2012 New Date & Time Related Functions
————
SELECT DATEFROMPARTS ( 2016, 10, 23 ) AS RealDate; — 2016-10-23
SELECT DATETIMEFROMPARTS ( 2016, 10, 23, 10, 10, 10, 500 ) AS RealDateTime; — 2016-10-23 10:10:10.500
SELECT EOMONTH(‘20140201’); — 2014-02-28
SELECT EOMONTH(‘20160201’); — 2016-02-29
SELECT EOMONTH(‘20160201’,1); — 2016-03-31
SELECT FORMAT ( getdate(), ‘yyyy/MM/dd hh:mm:ss tt’, ‘en-US’ ); — 2016/07/30 03:39:48 AM
SELECT FORMAT ( getdate(), ‘d’, ‘en-US’ ); — 7/30/2016
SELECT PARSE(‘SAT, 13 December 2014’ AS datetime USING ‘en-US’) AS [Date&Time];
— 2014-12-13 00:00:00.000
SELECT TRY_PARSE(‘SAT, 13 December 2014’ AS datetime USING ‘en-US’) AS [Date&Time];
— 2014-12-13 00:00:00.000
SELECT TRY_CONVERT(datetime, ’13 December 2014′ ) AS [Date&Time]; — 2014-12-13 00:00:00.000
SELECT CONVERT(datetime2, sysdatetime()); AS [DateTime2]; — 2016-02-12 13:09:24.0642891
————
— SQL convert seconds to HH:MM:SS – sql times format – sql hh mm
DECLARE @Seconds INT
SET @Seconds = 20000
SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)
/* HH MM SS
5 33 20 */
————
— SQL Server Date Only from DATETIME column – get date only
— T-SQL just date – truncate time from datetime – remove time part
————
DECLARE @Now datetime = CURRENT_TIMESTAMP — getdate()
SELECT DateAndTime = @Now — Date portion and Time portion
,DateString = REPLACE(LEFT(CONVERT (varchar, @Now, 112),10),’ ‘,’-‘)
,[Date] = CONVERT(DATE, @Now) — SQL Server 2008 and on – date part
,Midnight1 = dateadd(day, datediff(day,0, @Now), 0)
,Midnight2 = CONVERT(DATETIME,CONVERT(int, @Now))
,Midnight3 = CONVERT(DATETIME,CONVERT(BIGINT,@Now) & (POWER(Convert(bigint,2),32)-1))
/* DateAndTime DateString Date Midnight1 Midnight2 Midnight3
2010-11-02 08:00:33.657 20101102 2010-11-02 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 */
————
— SQL Server 2008 convert datetime to date – sql yyyy mm dd
SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),
Today = CONVERT(date, getdate())
FROM AdventureWorks2008.Sales.SalesOrderHeader
ORDER BY newid();
/* OrderDate Today
2004-02-15 2012-06-18 …..*/
————
— SQL date yyyy mm dd – sqlserver yyyy mm dd – date format yyyymmdd
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
/* YYYY/MM/DD
2015/07/11 */
SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]
/* YYYYMMDD
20150711 */
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),’/’,’ ‘) AS [YYYY MM DD]
/* YYYY MM DD
2015 07 11 */
— Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),’ ‘,’-‘))
— 07-MAR-14
————
— SQL convert date string to datetime – time set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,’07-10-2012′,110) — Jul 10 2012 12:00AM
PRINT CONVERT(datetime,’2012/07/10′,111) — Jul 10 2012 12:00AM
PRINT CONVERT(datetime,’20120710′, 112) — Jul 10 2012 12:00AM
————
— UNIX to SQL Server datetime conversion
declare @UNIX bigint = 1477216861;
select dateadd(ss,@UNIX,’19700101’); — 2016-10-23 10:01:01.000
————
— String to date conversion – sql date yyyy mm dd – sql date formatting
— SQL Server cast string to date – sql convert date to datetime
SELECT [Date] = CAST (@DateValue AS datetime)
— 2012-07-18 00:00:00.000
— SQL convert string date to different style – sql date string formatting
SELECT CONVERT(varchar, CONVERT(datetime, ‘20140508’), 100)
— May 8 2014 12:00AM
— SQL Server convert date to integer
DECLARE @Date datetime; SET @Date = getdate();
SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);
— Result: 20161225
— SQL Server convert integer to datetime
DECLARE @iDate int
SET @iDate = 20151225
SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)
— 2015-12-25 00:00:00.000
— Alternates: date-only datetime values
— SQL Server floor date – sql convert datetime
SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))
— SQL Server cast string to datetime
— SQL Server datetime to string convert
SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)
— SQL Server dateadd function – T-SQL datediff function
— SQL strip time from date – MSSQL strip time from datetime
SELECT getdate() ,dateadd(dd, datediff(dd, 0, getdate()), 0)
— Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000
— String date – 10 bytes of storage
SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)
SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)
— Same results: 01-02-2012
— SQL Server cast datetime as string – sql datetime formatting
SELECT stringDateTime=CAST (getdate() as varchar) — Dec 29 2012 3:47AM
———-
— SQL date range BETWEEN operator
———-
— SQL date range select – date range search – T-SQL date range query
— Count Sales Orders for 2003 OCT-NOV
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = convert(DATETIME,’10/01/2003′,101)
SET @EndDate = convert(DATETIME,’11/30/2003′,101)
SELECT @StartDate, @EndDate
— 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000
SELECT dateadd(DAY,1,@EndDate),
dateadd(ms,-3,dateadd(DAY,1,@EndDate))
— 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997
— MSSQL date range select using >= and <
SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
FROM Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate AND OrderDate < dateadd(DAY,1,@EndDate)
/* Sales Orders for 2003 OCT-NOV
3668 */
— Equivalent date range query using BETWEEN comparison
— It requires a bit of trick programming
SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @StartDate AND dateadd(ms,-3,dateadd(DAY,1,@EndDate))
— 3668
USE AdventureWorks;
— SQL between string dates
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN ‘20040201’ AND ‘20040210’ — Result: 108
— SQL BETWEEN dates without time – time stripped – time removed – date part only
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE datediff(dd,0,OrderDate)
BETWEEN datediff(dd,0,’20040201 12:11:39′) AND datediff(dd,0,’20040210 14:33:19′)
— 108
— BETWEEN is equivalent to >=…AND….<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN ‘2004-02-01 00:00:00.000’ AND ‘2004-02-10 00:00:00.000’
/* Orders with OrderDates
‘2004-02-10 00:00:01.000’ – 1 second after midnight (12:00AM)
‘2004-02-10 00:01:00.000’ – 1 minute after midnight
‘2004-02-10 01:00:00.000’ – 1 hour after midnight
are not included in the two queries above. */
— To include the entire day of 2004-02-10 use:
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= ‘20040201’ AND OrderDate < ‘20040211’
———-
— Calculate week ranges in a year
———-
DECLARE @Year INT = ‘2016’;
WITH cteDays AS (SELECT DayOfYear=Dateadd(dd, number,
CONVERT(DATE, CONVERT(char(4),@Year)+’0101′))
FROM master.dbo.spt_values WHERE type=’P’),
CTE AS (SELECT DayOfYear, WeekOfYear=DATEPART(week,DayOfYear)
FROM cteDays WHERE YEAR(DayOfYear)= @YEAR)
SELECT WeekOfYear, StartOfWeek=MIN(DayOfYear), EndOfWeek=MAX(DayOfYear)
FROM CTE GROUP BY WeekOfYear ORDER BY WeekOfYear
————
— Date validation function ISDATE – returns 1 or 0 – SQL datetime functions
————
DECLARE @StringDate varchar(32)
SET @StringDate = ‘2011-03-15 18:50’
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT ‘VALID DATE: ‘ + @StringDate
ELSE
PRINT ‘INVALID DATE: ‘ + @StringDate
GO
— Result: VALID DATE: 2011-03-15 18:50
DECLARE @StringDate varchar(32)
SET @StringDate = ‘20112-03-15 18:50’
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT ‘VALID DATE: ‘ + @StringDate
ELSE PRINT ‘INVALID DATE: ‘ + @StringDate
— Result: INVALID DATE: 20112-03-15 18:50
— First and last day of date periods – SQL Server 2008 and on code
DECLARE @Date DATE = ‘20161023’
SELECT ReferenceDate = @Date
SELECT FirstDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date),0))
SELECT LastDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date)+1,-1))
SELECT FDofSemester = CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2),0))
SELECT LastDayOfSemester
= CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2)+2,-1))
SELECT FirstDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0, @Date),0))
— 2016-10-01
SELECT LastDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0,@Date)+1,-1))
— 2016-12-31
SELECT FirstDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date),0))
SELECT LastDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date)+1,-1))
SELECT FirstDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date),0))
SELECT LastDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date)+1,-1))
— 2016-10-30
— Month sequence generator – sequential numbers / dates
DECLARE @Date date = ‘2000-01-01′
SELECT MonthStart=dateadd(MM, number, @Date)
FROM master.dbo.spt_values
WHERE type=’P’ AND dateadd(MM, number, @Date) <= CURRENT_TIMESTAMP
ORDER BY MonthStart
/* MonthStart
2000-01-01
2000-02-01
2000-03-01 ….*/
