How to format datetime & date in Sql Server 2005

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

  1. – Microsoft SQL Server T-SQL date and datetime formats  
  2.   
  3. – Date time formats – mssql datetime  
  4.   
  5. – MSSQL getdate returns current system date and time in standard internal format  
  6.   
  7. SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)  
  8.   
  9. – Oct 2 2008 11:01AM  
  10.   
  11. SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008  
  12.   
  13. SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02  
  14.   
  15. SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy  
  16.   
  17. SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy  
  18.   
  19. SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy  
  20.   
  21. SELECT convert(varchar, getdate(), 106) – dd mon yyyy  
  22.   
  23. SELECT convert(varchar, getdate(), 107) – mon dd, yyyy  
  24.   
  25. SELECT convert(varchar, getdate(), 108) – hh:mm:ss  
  26.   
  27. SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)  
  28.   
  29. – Oct 2 2008 11:02:44:013AM  
  30.   
  31. SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy  
  32.   
  33. SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd  
  34.   
  35. SELECT convert(varchar, getdate(), 112) – yyyymmdd  
  36.   
  37. SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm  
  38.   
  39. – 02 Oct 2008 11:02:07:577  
  40.   
  41. SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)  
  42.   
  43. SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)  
  44.   
  45. SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm  
  46.   
  47. SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm  
  48.   
  49. – 2008-10-02T10:52:47.513  
  50.   
  51. – SQL create different date styles with t-sql string functions  
  52.   
  53. SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd  
  54.   
  55. SELECT convert(varchar(7), getdate(), 126) – yyyy-mm  
  56.   
  57. SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy  
  58.   
  59. ————  
  60.   
  61. – SQL Server date formatting function – convert datetime to string  
  62.   
  63. ————  
  64.   
  65. – SQL datetime functions  
  66.   
  67. – SQL Server date formats  
  68.   
  69. – T-SQL convert dates  
  70.   
  71. – Formatting dates sql server  
  72.   
  73. CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))  
  74.   
  75. RETURNS VARCHAR(32)  
  76.   
  77. AS  
  78.   
  79. BEGIN  
  80.   
  81. DECLARE @StringDate VARCHAR(32)  
  82.   
  83. SET @StringDate = @FormatMask  
  84.   
  85. IF (CHARINDEX (‘YYYY’,@StringDate) > 0)  
  86.   
  87. SET @StringDate = REPLACE(@StringDate, ‘YYYY’,  
  88.   
  89. DATENAME(YY, @Datetime))  
  90.   
  91. IF (CHARINDEX (‘YY’,@StringDate) > 0)  
  92.   
  93. SET @StringDate = REPLACE(@StringDate, ‘YY’,  
  94.   
  95. RIGHT(DATENAME(YY, @Datetime),2))  
  96.   
  97. IF (CHARINDEX (‘Month’,@StringDate) > 0)  
  98.   
  99. SET @StringDate = REPLACE(@StringDate, ‘Month’,  
  100.   
  101. DATENAME(MM, @Datetime))  
  102.   
  103. IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)  
  104.   
  105. SET @StringDate = REPLACE(@StringDate, ‘MON’,  
  106.   
  107. LEFT(UPPER(DATENAME(MM, @Datetime)),3))  
  108.   
  109. IF (CHARINDEX (‘Mon’,@StringDate) > 0)  
  110.   
  111. SET @StringDate = REPLACE(@StringDate, ‘Mon’,  
  112.   
  113. LEFT(DATENAME(MM, @Datetime),3))  
  114.   
  115. IF (CHARINDEX (‘MM’,@StringDate) > 0)  
  116.   
  117. SET @StringDate = REPLACE(@StringDate, ‘MM’,  
  118.   
  119. RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))  
  120.   
  121. IF (CHARINDEX (‘M’,@StringDate) > 0)  
  122.   
  123. SET @StringDate = REPLACE(@StringDate, ‘M’,  
  124.   
  125. CONVERT(VARCHAR,DATEPART(MM, @Datetime)))  
  126.   
  127. IF (CHARINDEX (‘DD’,@StringDate) > 0)  
  128.   
  129. SET @StringDate = REPLACE(@StringDate, ‘DD’,  
  130.   
  131. RIGHT(‘0’+DATENAME(DD, @Datetime),2))  
  132.   
  133. IF (CHARINDEX (‘D’,@StringDate) > 0)  
  134.   
  135. SET @StringDate = REPLACE(@StringDate, ‘D’,  
  136.   
  137. DATENAME(DD, @Datetime))  
  138.   
  139. RETURN @StringDate  
  140.   
  141. END  
  142.   
  143. GO  
  144.   
  145. – Microsoft SQL Server date format function test  
  146.   
  147. – MSSQL formatting dates  
  148.   
  149. SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) – 01/03/2012  
  150.   
  151. SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) – 03/01/2012  
  152.   
  153. SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) – 1/03/2012  
  154.   
  155. SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) – 1/3/2012  
  156.   
  157. SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) – 1/3/12  
  158.   
  159. SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) – 01/03/12  
  160.   
  161. SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) – JAN 03, 2012  
  162.   
  163. SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) – Jan 03, 2012  
  164.   
  165. SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) – January 03, 2012  
  166.   
  167. SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) – 2012/01/03  
  168.   
  169. SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) – 20120103  
  170.   
  171. SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) – 2012-01-03  
  172.   
  173. – CURRENT_TIMESTAMP returns current system date and time in standard internal format  
  174.   
  175. SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’) – 12.01.03  
  176.   
  177. GO  
  178.   
  179. ————  
  180.   
  181. /***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/  
  182.   
  183. – SQL format datetime  
  184.   
  185. – Default format: Oct 23 2006 10:40AM  
  186.   
  187. SELECT [Default]=CONVERT(varchar,GETDATE(),100)  
  188.   
  189. – US-Style format: 10/23/2006  
  190.   
  191. SELECT [US-Style]=CONVERT(char,GETDATE(),101)  
  192.   
  193. – ANSI format: 2006.10.23  
  194.   
  195. SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)  
  196.   
  197. – UK-Style format: 23/10/2006  
  198.   
  199. SELECT [UK-Style]=CONVERT(char,GETDATE(),103)  
  200.   
  201. – German format: 23.10.2006  
  202.   
  203. SELECT [German]=CONVERT(varchar,GETDATE(),104)  
  204.   
  205. – ISO format: 20061023  
  206.   
  207. SELECT ISO=CONVERT(varchar,GETDATE(),112)  
  208.   
  209. – ISO8601 format: 2008-10-23T19:20:16.003  
  210.   
  211. SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)  
  212.   
  213. ————  
  214.   
  215. – SQL Server datetime formats  
  216.   
  217. – Century date format MM/DD/YYYY usage in a query  
  218.   
  219. – Format dates SQL Server 2005  
  220.   
  221. SELECT TOP (1)  
  222.   
  223. SalesOrderID,  
  224.   
  225. OrderDate = CONVERT(char(10), OrderDate, 101),  
  226.   
  227. OrderDateTime = OrderDate  
  228.   
  229. FROM AdventureWorks.Sales.SalesOrderHeader  
  230.   
  231. /* Result  
  232.   
  233. SalesOrderID OrderDate OrderDateTime  
  234.   
  235. 43697 07/01/2001 2001-07-01 00:00:00.000  
  236.   
  237. */  
  238.   
  239. – SQL update datetime column  
  240.   
  241. – SQL datetime DATEADD  
  242.   
  243. UPDATE Production.Product  
  244.   
  245. SET ModifiedDate=DATEADD(dd,1, ModifiedDate)  
  246.   
  247. WHERE ProductID = 1001  
  248.   
  249. – MM/DD/YY date format  
  250.   
  251. – Datetime format sql  
  252.   
  253. SELECT TOP (1)  
  254.   
  255. SalesOrderID,  
  256.   
  257. OrderDate = CONVERT(varchar(8), OrderDate, 1),  
  258.   
  259. OrderDateTime = OrderDate  
  260.   
  261. FROM AdventureWorks.Sales.SalesOrderHeader  
  262.   
  263. ORDER BY SalesOrderID desc  
  264.   
  265. /* Result  
  266.   
  267. SalesOrderID OrderDate OrderDateTime  
  268.   
  269. 75123 07/31/04 2004-07-31 00:00:00.000  
  270.   
  271. */  
  272.   
  273. – Combining different style formats for date & time  
  274.   
  275. – Datetime formats  
  276.   
  277. – Datetime formats sql  
  278.   
  279. DECLARE @Date DATETIME  
  280.   
  281. SET @Date = ‘2015-12-22 03:51 PM’  
  282.   
  283. SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)  
  284.   
  285. – Result: 12-22-2015 3:51PM  
  286.   
  287. – Microsoft SQL Server cast datetime to string  
  288.   
  289. SELECT stringDateTime=CAST (getdate() as varchar)  
  290.   
  291. – Result: Dec 29 2012 3:47AM  
  292.   
  293. ————  
  294.   
  295. – SQL Server date and time functions overview  
  296.   
  297. ————  
  298.   
  299. – SQL Server CURRENT_TIMESTAMP function  
  300.   
  301. – SQL Server datetime functions  
  302.   
  303. – local NYC – EST – Eastern Standard Time zone  
  304.   
  305. – SQL DATEADD function – SQL DATEDIFF function  
  306.   
  307. SELECT CURRENT_TIMESTAMP – 2012-01-05 07:02:10.577  
  308.   
  309. – SQL Server DATEADD function  
  310.   
  311. SELECT DATEADD(month,2,‘2012-12-09′) – 2013-02-09 00:00:00.000  
  312.   
  313. – SQL Server DATEDIFF function  
  314.   
  315. SELECT DATEDIFF(day,‘2012-12-09′,‘2013-02-09′) – 62  
  316.   
  317. – SQL Server DATENAME function  
  318.   
  319. SELECT DATENAME(month, ‘2012-12-09′) – December  
  320.   
  321. SELECT DATENAME(weekday, ‘2012-12-09′) – Sunday  
  322.   
  323. – SQL Server DATEPART function  
  324.   
  325. SELECT DATEPART(month, ‘2012-12-09′) – 12  
  326.   
  327. – SQL Server DAY function  
  328.   
  329. SELECT DAY(‘2012-12-09′) – 9  
  330.   
  331. – SQL Server GETDATE function  
  332.   
  333. – local NYC – EST – Eastern Standard Time zone  
  334.   
  335. SELECT GETDATE() – 2012-01-05 07:02:10.577  
  336.   
  337. – SQL Server GETUTCDATE function  
  338.   
  339. – London – Greenwich Mean Time  
  340.   
  341. SELECT GETUTCDATE() – 2012-01-05 12:02:10.577  
  342.   
  343. – SQL Server MONTH function  
  344.   
  345. SELECT MONTH(‘2012-12-09′) – 12  
  346.   
  347. – SQL Server YEAR function  
  348.   
  349. SELECT YEAR(‘2012-12-09′) – 2012  
  350.   
  351. ————  
  352.   
  353. – T-SQL Date and time function application  
  354.   
  355. – CURRENT_TIMESTAMP and getdate() are the same in T-SQL  
  356.   
  357. ————  
  358.   
  359. – SQL first day of the month  
  360.   
  361. – SQL first date of the month  
  362.   
  363. – SQL first day of current month – 2012-01-01 00:00:00.000  
  364.   
  365. SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))  
  366.   
  367. – SQL last day of the month  
  368.   
  369. – SQL last date of the month  
  370.   
  371. – SQL last day of current month – 2012-01-31 00:00:00.000  
  372.   
  373. SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))  
  374.   
  375. – SQL first day of last month  
  376.   
  377. – SQL first day of previous month – 2011-12-01 00:00:00.000  
  378.   
  379. SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))  
  380.   
  381. – SQL last day of last month  
  382.   
  383. – SQL last day of previous month – 2011-12-31 00:00:00.000  
  384.   
  385. SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))  
  386.   
  387. – SQL first day of next month – 2012-02-01 00:00:00.000  
  388.   
  389. SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))  
  390.   
  391. – SQL last day of next month – 2012-02-28 00:00:00.000  
  392.   
  393. SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))  
  394.   
  395. GO  
  396.   
  397. – SQL first day of a month – 2012-10-01 00:00:00.000  
  398.   
  399. DECLARE @Date datetime; SET @Date = ‘2012-10-23′  
  400.   
  401. SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))  
  402.   
  403. GO  
  404.   
  405. – SQL last day of a month – 2012-03-31 00:00:00.000  
  406.   
  407. DECLARE @Date datetime; SET @Date = ‘2012-03-15′  
  408.   
  409. SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))  
  410.   
  411. GO  
  412.   
  413. – SQL first day of year  
  414.   
  415. – SQL first day of the year – 2012-01-01 00:00:00.000  
  416.   
  417. SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)  
  418.   
  419. – SQL last day of year  
  420.   
  421. – SQL last day of the year – 2012-12-31 00:00:00.000  
  422.   
  423. SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,  
  424.   
  425. DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))  
  426.   
  427. – SQL last day of last year  
  428.   
  429. – SQL last day of previous year – 2011-12-31 00:00:00.000  
  430.   
  431. SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))  
  432.   
  433. GO  
  434.   
  435. – SQL calculate age in years, months, days  
  436.   
  437. – SQL table-valued function  
  438.   
  439. – SQL user-defined function – UDF  
  440.   
  441. – SQL Server age calculation – date difference  
  442.   
  443. – Format dates SQL Server 2008  
  444.   
  445. USE AdventureWorks2008;  
  446.   
  447. GO  
  448.   
  449. CREATE FUNCTION fnAge (@BirthDate DATETIME)  
  450.   
  451. RETURNS @Age TABLE(Years INT,  
  452.   
  453. Months INT,  
  454.   
  455. Days INT)  
  456.   
  457. AS  
  458.   
  459. BEGIN  
  460.   
  461. DECLARE @EndDate DATETIME, @Anniversary DATETIME  
  462.   
  463. SET @EndDate = Getdate()  
  464.   
  465. SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)  
  466.   
  467. INSERT @Age  
  468.   
  469. SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE  
  470.   
  471. WHEN @Anniversary > @EndDate THEN 1  
  472.   
  473. ELSE 0  
  474.   
  475. END), 0, 0  
  476.   
  477. UPDATE @Age SET Months = Month(@EndDate - @Anniversary) - 1  
  478.   
  479. UPDATE @Age SET Days = Day(@EndDate - @Anniversary) - 1  
  480.   
  481. RETURN  
  482.   
  483. END  
  484.   
  485. GO  
  486.   
  487. – Test table-valued UDF  
  488.   
  489. SELECT * FROM fnAge(‘1956-10-23′)  
  490.   
  491. SELECT * FROM dbo.fnAge(‘1956-10-23′)  
  492.   
  493. /* Results  
  494.   
  495. Years Months Days  
  496.   
  497. 52 4 1  
  498.   
  499. */  
  500.   
  501. ———-  
  502.   
  503. – SQL date range between  
  504.   
  505. ———-  
  506.   
  507. – SQL between dates  
  508.   
  509. USE AdventureWorks;  
  510.   
  511. – SQL between  
  512.   
  513. SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader  
  514.   
  515. WHERE OrderDate BETWEEN ‘20040301’ AND ‘20040315’  
  516.   
  517. – Result: 108  
  518.   
  519. – BETWEEN operator is equivalent to >=…AND….<=  
  520.   
  521. SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader  
  522.   
  523. WHERE OrderDate  
  524.   
  525. BETWEEN ‘2004-03-01 00:00:00.000′ AND ‘2004-03-15 00:00:00.000′  
  526.   
  527. /*  
  528.   
  529. Orders with OrderDates  
  530.   
  531. ‘2004-03-15 00:00:01.000′ – 1 second after midnight (12:00AM)  
  532.   
  533. ‘2004-03-15 00:01:00.000′ – 1 minute after midnight  
  534.   
  535. ‘2004-03-15 01:00:00.000′ – 1 hour after midnight  
  536.   
  537. are not included in the two queries above.  
  538.   
  539. */  
  540.   
  541. – To include the entire day of 2004-03-15 use the following two solutions  
  542.   
  543. SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader  
  544.   
  545. WHERE OrderDate >= ‘20040301’ AND OrderDate < ‘20040316’  
  546.   
  547. – SQL between with DATE type (SQL Server 2008)  
  548.   
  549. SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader  
  550.   
  551. WHERE CONVERT(DATE, OrderDate) BETWEEN ‘20040301’ AND ‘20040315’  
  552.   
  553. ———-  
  554.   
  555. – Non-standard format conversion: 2011 December 14  
  556.   
  557. – SQL datetime to string  
  558.   
  559. SELECT [YYYY Month DD] =  
  560.   
  561. CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+  
  562.   
  563. DATENAME(MM, GETDATE()) + ‘ ‘ +  
  564.   
  565. CAST(DAY(GETDATE()) AS VARCHAR(2))  
  566.   
  567. – Converting datetime to YYYYMMDDHHMMSS format: 20121214172638  
  568.   
  569. SELECT replace(convert(varchar, getdate(),111),‘/’,”) +  
  570.   
  571. replace(convert(varchar, getdate(),108),‘:’,”)  
  572.   
  573. – Datetime custom format conversion to YYYY_MM_DD  
  574.   
  575. select CurrentDate=rtrim(year(getdate())) + ‘_’ +  
  576.   
  577. right(‘0’ + rtrim(month(getdate())),2) + ‘_’ +  
  578.   
  579. right(‘0’ + rtrim(day(getdate())),2)  
  580.   
  581. – Converting seconds to HH:MM:SS format  
  582.   
  583. declare @Seconds int  
  584.   
  585. set @Seconds = 10000  
  586.   
  587. select TimeSpan=right(‘0’ +rtrim(@Seconds / 3600),2) + ‘:’ +  
  588.   
  589. right(‘0’ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +  
  590.   
  591. right(‘0’ + rtrim(@Seconds % 60),2)  
  592.   
  593. – Result: 02:46:40  
  594.   
  595. – Test result  
  596.   
  597. select 2*3600 + 46*60 + 40  
  598.   
  599. – Result: 10000  
  600.   
  601. – Set the time portion of a datetime value to 00:00:00.000  
  602.   
  603. – SQL strip time from date  
  604.   
  605. – SQL strip time from datetime  
  606.   
  607. SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)  
  608.   
  609. – Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000  
  610.   
  611. /*******  
  612.   
  613. VALID DATE RANGES FOR DATE/DATETIME DATA TYPES  
  614.   
  615. SMALLDATETIME date range:  
  616.   
  617. January 1, 1900 through June 6, 2079  
  618.   
  619. DATETIME date range:  
  620.   
  621. January 1, 1753 through December 31, 9999  
  622.   
  623. DATETIME2 date range (SQL Server 2008):  
  624.   
  625. January 1,1 AD through December 31, 9999 AD  
  626.   
  627. DATE date range (SQL Server 2008):  
  628.   
  629. January 1, 1 AD through December 31, 9999 AD  
  630.   
  631. *******/  
  632.   
  633. – Selecting with CONVERT into different styles  
  634.   
  635. – Note: Only Japan & ISO styles can be used in ORDER BY  
  636.   
  637. SELECT TOP(1)  
  638.   
  639. Italy = CONVERT(varchar, OrderDate, 105)  
  640.   
  641. , USA = CONVERT(varchar, OrderDate, 110)  
  642.   
  643. , Japan = CONVERT(varchar, OrderDate, 111)  
  644.   
  645. , ISO = CONVERT(varchar, OrderDate, 112)  
  646.   
  647. FROM AdventureWorks.Purchasing.PurchaseOrderHeader  
  648.   
  649. ORDER BY PurchaseOrderID DESC  
  650.   
  651. /* Results  
  652.   
  653. Italy USA Japan ISO  
  654.   
  655. 25-07-2004 07-25-2004 2004/07/25 20040725  
  656.   
  657. */  
  658.   
  659. – SQL Server convert date to integer  
  660.   
  661. DECLARE @Datetime datetime  
  662.   
  663. SET @Datetime = ‘2012-10-23 10:21:05.345′  
  664.   
  665. SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)  
  666.   
  667. – Result: 20121023  
  668.   
  669. – SQL Server convert integer to datetime  
  670.   
  671. DECLARE @intDate int  
  672.   
  673. SET @intDate = 20120315  
  674.   
  675. SELECT IntegerToDatetime = CAST(CAST(@intDate as varcharas datetime)  
  676.   
  677. – Result: 2012-03-15 00:00:00.000  
  678.   
  679. ————  
  680.   
  681. – SQL Server CONVERT script applying table INSERT/UPDATE  
  682.   
  683. ————  
  684.   
  685. – SQL Server convert date  
  686.   
  687. – Datetime column is converted into date only string column  
  688.   
  689. USE tempdb;  
  690.   
  691. GO  
  692.   
  693. CREATE TABLE sqlConvertDateTime (  
  694.   
  695. DatetimeCol datetime,  
  696.   
  697. DateCol char(8));  
  698.   
  699. INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()  
  700.   
  701. UPDATE sqlConvertDateTime  
  702.   
  703. SET DateCol = CONVERT(char(10), DatetimeCol, 112)  
  704.   
  705. SELECT * FROM sqlConvertDateTime  
  706.   
  707. – SQL Server convert datetime  
  708.   
  709. – The string date column is converted into datetime column  
  710.   
  711. UPDATE sqlConvertDateTime  
  712.   
  713. SET DatetimeCol = CONVERT(Datetime, DateCol, 112)  
  714.   
  715. SELECT * FROM sqlConvertDateTime  
  716.   
  717. – Adding a day to the converted datetime column with DATEADD  
  718.   
  719. UPDATE sqlConvertDateTime  
  720.   
  721. SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))  
  722.   
  723. SELECT * FROM sqlConvertDateTime  
  724.   
  725. – Equivalent formulation  
  726.   
  727. – SQL Server cast datetime  
  728.   
  729. UPDATE sqlConvertDateTime  
  730.   
  731. SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))  
  732.   
  733. SELECT * FROM sqlConvertDateTime  
  734.   
  735. GO  
  736.   
  737. DROP TABLE sqlConvertDateTime  
  738.   
  739. GO  
  740.   
  741. /* First results  
  742.   
  743. DatetimeCol DateCol  
  744.   
  745. 2014-12-25 16:04:15.373 20141225 */  
  746.   
  747. /* Second results:  
  748.   
  749. DatetimeCol DateCol  
  750.   
  751. 2014-12-25 00:00:00.000 20141225 */  
  752.   
  753. /* Third results:  
  754.   
  755. DatetimeCol DateCol  
  756.   
  757. 2014-12-26 00:00:00.000 20141225 */  
  758.   
  759. ————  
  760.   
  761. – SQL month sequence – SQL date sequence generation with table variable  
  762.   
  763. – SQL Server cast string to datetime – SQL Server cast datetime to string  
  764.   
  765. – SQL Server insert default values method  
  766.   
  767. DECLARE @Sequence table (Sequence int identity(1,1))  
  768.   
  769. DECLARE @i intSET @i = 0  
  770.   
  771. DECLARE @StartDate datetime;  
  772.   
  773. SET @StartDate = CAST(CONVERT(varcharyear(getdate()))+  
  774.   
  775. RIGHT(‘0’+convert(varchar,month(getdate())),2) + ’01’ AS DATETIME)  
  776.   
  777. WHILE ( @i < 120)  
  778.   
  779. BEGIN  
  780.   
  781. INSERT @Sequence DEFAULT VALUES  
  782.   
  783. SET @i = @i + 1  
  784.   
  785. END  
  786.   
  787. SELECT MonthSequence = CAST(DATEADD(monthSequence,@StartDate) AS varchar)  
  788.   
  789. FROM @Sequence  
  790.   
  791. GO  
  792.   
  793. /* Partial results:  
  794.   
  795. MonthSequence  
  796.   
  797. Jan 1 2012 12:00AM  
  798.   
  799. Feb 1 2012 12:00AM  
  800.   
  801. Mar 1 2012 12:00AM  
  802.   
  803. Apr 1 2012 12:00AM  
  804.   
  805. */  
  806.   
  807. ————  
  808.   
  809. ————  
  810.   
  811. – SQL Server Server datetime internal storage  
  812.   
  813. – SQL Server datetime formats  
  814.   
  815. ————  
  816.   
  817. – SQL Server datetime to hex  
  818.   
  819. SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))  
  820.   
  821. /* Results  
  822.   
  823. Now HexNow  
  824.   
  825. 2009-01-02 17:35:59.297 0x00009B850122092D  
  826.   
  827. */  
  828.   
  829. – SQL Server date part – left 4 bytes – Days since 1900-01-01  
  830.   
  831. SELECT Now=DATEADD(DAYCONVERT(INT, 0x00009B85), ‘19000101’)  
  832.   
  833. GO  
  834.   
  835. – Result: 2009-01-02 00:00:00.000  
  836.   
  837. – SQL time part – right 4 bytes – milliseconds since midnight  
  838.   
  839. – 1000/300 is an adjustment factor  
  840.   
  841. – SQL dateadd to Midnight  
  842.   
  843. SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), ‘2009-01-02′)  
  844.   
  845. GO  
  846.   
  847. – Result: 2009-01-02 17:35:59.290  
  848.   
  849. ————  
  850.   
  851. ————  
  852.   
  853. – String date and datetime date&time columns usage  
  854.   
  855. – SQL Server datetime formats in tables  
  856.   
  857. ————  
  858.   
  859. USE tempdb;  
  860.   
  861. SET NOCOUNT ON;  
  862.   
  863. – SQL Server select into table create  
  864.   
  865. SELECT TOP (5)  
  866.   
  867. FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),  
  868.   
  869. BirthDate = CONVERT(char(8), BirthDate,112),  
  870.   
  871. ModifiedDate = getdate()  
  872.   
  873. INTO Employee  
  874.   
  875. FROM AdventureWorks.HumanResources.Employee e  
  876.   
  877. INNER JOIN AdventureWorks.Person.Contact c  
  878.   
  879. ON c.ContactID = e.ContactID  
  880.   
  881. ORDER BY EmployeeID  
  882.   
  883. GO  
  884.   
  885. – SQL Server alter table  
  886.   
  887. ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL  
  888.   
  889. GO  
  890.   
  891. ALTER TABLE Employee  
  892.   
  893. ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )  
  894.   
  895. GO  
  896.   
  897. /* Results  
  898.   
  899. Table definition for the Employee table  
  900.   
  901. Note: BirthDate is string date (only)  
  902.   
  903. CREATE TABLE dbo.Employee(  
  904.   
  905. FullName nvarchar(50) NOT NULL PRIMARY KEY,  
  906.   
  907. BirthDate char(8) NULL,  
  908.   
  909. ModifiedDate datetime NOT NULL  
  910.   
  911. )  
  912.   
  913. */  
  914.   
  915. SELECT * FROM Employee ORDER BY FullName  
  916.   
  917. GO  
  918.   
  919. /* Results  
  920.   
  921. FullName BirthDate ModifiedDate  
  922.   
  923. Guy Gilbert 19720515 2009-01-03 10:10:19.217  
  924.   
  925. Kevin Brown 19770603 2009-01-03 10:10:19.217  
  926.   
  927. Rob Walters 19650123 2009-01-03 10:10:19.217  
  928.   
  929. Roberto Tamburello 19641213 2009-01-03 10:10:19.217  
  930.   
  931. Thierry D’Hers 19490829 2009-01-03 10:10:19.217  
  932.   
  933. */  
  934.   
  935. – SQL Server age  
  936.   
  937. SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),  
  938.   
  939. RowMaintenanceDate = CAST (ModifiedDate AS varchar)  
  940.   
  941. FROM Employee ORDER BY FullName  
  942.   
  943. GO  
  944.   
  945. /* Results  
  946.   
  947. FullName Age RowMaintenanceDate  
  948.   
  949. Guy Gilbert 37 Jan 3 2009 10:10AM  
  950.   
  951. Kevin Brown 32 Jan 3 2009 10:10AM  
  952.   
  953. Rob Walters 44 Jan 3 2009 10:10AM  
  954.   
  955. Roberto Tamburello 45 Jan 3 2009 10:10AM  
  956.   
  957. Thierry D’Hers 60 Jan 3 2009 10:10AM  
  958.   
  959. */  
  960.   
  961. – SQL Server age of Rob Walters on specific dates  
  962.   
  963. – SQL Server string to datetime implicit conversion with DATEADD  
  964.   
  965. SELECT AGE50DATE = DATEADD(YY, 50, ‘19650123’)  
  966.   
  967. GO  
  968.   
  969. – Result: 2015-01-23 00:00:00.000  
  970.   
  971. – SQL Server datetime to string, Italian format for ModifiedDate  
  972.   
  973. – SQL Server string to datetime implicit conversion with DATEDIFF  
  974.   
  975. SELECT FullName,  
  976.   
  977. AgeDEC31 = DATEDIFF(YEAR, BirthDate, ‘20141231’),  
  978.   
  979. AgeJAN01 = DATEDIFF(YEAR, BirthDate, ‘20150101’),  
  980.   
  981. AgeJAN23 = DATEDIFF(YEAR, BirthDate, ‘20150123’),  
  982.   
  983. AgeJAN24 = DATEDIFF(YEAR, BirthDate, ‘20150124’),  
  984.   
  985. ModDate = CONVERT(varchar, ModifiedDate, 105)  
  986.   
  987. FROM Employee  
  988.   
  989. WHERE FullName = ‘Rob Walters’  
  990.   
  991. ORDER BY FullName  
  992.   
  993. GO  
  994.   
  995. /* Results  
  996.   
  997. Important Note: age increments on Jan 1 (not as commonly calculated)  
  998.   
  999. FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDate  
  1000.   
  1001. Rob Walters 49 50 50 50 03-01-2009  
  1002.   
  1003. */  
  1004.   
  1005. ————  
  1006.   
  1007. – SQL combine integer date & time into datetime  
  1008.   
  1009. ————  
  1010.   
  1011. – Datetime format sql  
  1012.   
  1013. – SQL stuff  
  1014.   
  1015. DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,  
  1016.   
  1017. DateAsINT int,  
  1018.   
  1019. TimeAsINT int  
  1020.   
  1021. )  
  1022.   
  1023. – NOTE: leading zeroes in time is for readability only!  
  1024.   
  1025. INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)  
  1026.   
  1027. INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)  
  1028.   
  1029. INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)  
  1030.   
  1031. INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)  
  1032.   
  1033. INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)  
  1034.   
  1035. INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)  
  1036.   
  1037. SELECT DateAsINT, TimeAsINT,  
  1038.   
  1039. CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+  
  1040.   
  1041. STUFF(STUFF ( RIGHT(REPLICATE(‘0’, 6) + CONVERT(varchar(6), TimeAsINT), 6),  
  1042.   
  1043. 3, 0, ‘:’), 6, 0, ‘:’)) AS DateTimeValue  
  1044.   
  1045. FROM @DateTimeAsINT  
  1046.   
  1047. ORDER BY ID  
  1048.   
  1049. GO  
  1050.   
  1051. /* Results  
  1052.   
  1053. DateAsINT TimeAsINT DateTimeValue  
  1054.   
  1055. 20121023 235959 2012-10-23 23:59:59.000  
  1056.   
  1057. 20121023 10204 2012-10-23 01:02:04.000  
  1058.   
  1059. 20121023 2350 2012-10-23 00:23:50.000  
  1060.   
  1061. 20121023 244 2012-10-23 00:02:44.000  
  1062.   
  1063. 20121023 50 2012-10-23 00:00:50.000  
  1064.   
  1065. 20121023 6 2012-10-23 00:00:06.000  
  1066.   
  1067. */  
  1068.   
  1069. ————