· 9 years ago · Oct 23, 2016, 02:32 AM
1-- SQL Server string to date / datetime conversion - datetime string format sql server
2-- MSSQL string to datetime conversion - convert char to date - convert varchar to date
3-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
4SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
5SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
6
7-- Without century (yy) string date conversion - convert string to datetime function
8SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)
9SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
10
11-- Convert string to datetime sql - convert string to date sql - sql dates format
12-- T-SQL convert string to datetime - SQL Server convert string to date
13SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
14SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
15SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
16SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
17SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
18-- mon types are nondeterministic conversions, dependent on language setting
19SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
20SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
21-- 2016-10-23 00:00:00.000
22SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
23-- 1900-01-01 20:10:44.000
24
25-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
26SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
27-- 2016-10-23 11:02:44.013
28SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
29SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
30-- YYYYMMDD ISO date format works at any language setting - international standard
31SELECT convert(datetime, '20161023')
32SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd
33-- 2016-10-23 00:00:00.000
34SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
35-- 2016-10-23 11:02:07.577
36SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
37-- 1900-01-01 20:10:25.300
38SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
39-- 2016-10-23 20:44:11.000
40SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
41-- 2016-10-23 20:44:11.500
42
43-- Style 126 is ISO 8601 format: international standard - works with any language setting
44SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
45-- 2008-10-23 18:52:47.513
46SELECT convert(datetime, N'23 شوال 1429 6:52:47:513PM', 130) -- Islamic/Hijri date
47SELECT convert(datetime, '23/10/1429 6:52:47:513PM', 131) -- Islamic/Hijri date
48
49-- Convert DDMMYYYY format to datetime - sql server to date / datetime
50SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
51-- 2016-01-31 00:00:00.000
52-- SQL Server T-SQL string to datetime conversion without century - some exceptions
53-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
54SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default
55SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
56SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
57SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
58SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German
59SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian
60SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.
61SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.
62SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss
63SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
64SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
65SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan
66SELECT convert(datetime, '161023', 12) -- yymmdd ISO
67SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
68SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)
69SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.
70SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC
71------------
72
73-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss
74-- String to datetime - mssql datetime - sql convert date - sql concatenate string
75DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)
76
77SELECT @DateValue = '20120718',
78 @TimeValue = '211920'
79SELECT @DateTimeValue =
80convert(varchar, convert(datetime, @DateValue), 111)
81+ ' ' + substring(@TimeValue, 1, 2)
82+ ':' + substring(@TimeValue, 3, 2)
83+ ':' + substring(@TimeValue, 5, 2)
84SELECT
85DateInput = @DateValue, TimeInput = @TimeValue,
86DateTimeOutput = @DateTimeValue;
87/*
88DateInput TimeInput DateTimeOutput
8920120718 211920 2012/07/18 21:19:20 */
90
91/* DATETIME 8 bytes internal storage structure
92 o 1st 4 bytes: number of days after the base date 1900-01-01
93 o 2nd 4 bytes: number of clock-ticks (3.33 milliseconds) since midnight
94
95DATETIME2 8 bytes (precision > 4) internal storage structure
96 o 1st byte: precision like 7
97 o middle 4 bytes: number of time units (100ns smallest) since midnight
98 o last 3 bytes: number of days after the base date 0001-01-01
99
100DATE 3 bytes internal storage structure
101 o 3 bytes integer: number of days after the first date 0001-01-01
102 o Note: hex byte order reversed
103
104SMALLDATETIME 4 bytes internal storage structure
105 o 1st 2 bytes: number of days after the base date 1900-01-01
106 o 2nd 2 bytes: number of minutes since midnight */
107
108SELECT CONVERT(binary(8), getdate()) -- 0x00009E4D 00C01272
109SELECT CONVERT(binary(4), convert(smalldatetime,getdate())) -- 0x9E4D 02BC
110
111-- This is how a datetime looks in 8 bytes
112DECLARE @dtHex binary(8)= 0x00009966002d3344;
113DECLARE @dt datetime = @dtHex
114SELECT @dt -- 2007-07-09 02:44:34.147
115------------ */
116
117------------
118-- SQL Server 2012 New Date & Time Related Functions
119------------
120SELECT DATEFROMPARTS ( 2016, 10, 23 ) AS RealDate; -- 2016-10-23
121
122SELECT DATETIMEFROMPARTS ( 2016, 10, 23, 10, 10, 10, 500 ) AS RealDateTime; -- 2016-10-23 10:10:10.500
123
124SELECT EOMONTH('20140201'); -- 2014-02-28
125SELECT EOMONTH('20160201'); -- 2016-02-29
126SELECT EOMONTH('20160201',1); -- 2016-03-31
127
128SELECT FORMAT ( getdate(), 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ); -- 2016/07/30 03:39:48 AM
129SELECT FORMAT ( getdate(), 'd', 'en-US' ); -- 7/30/2016
130
131SELECT PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time];
132-- 2014-12-13 00:00:00.000
133
134SELECT TRY_PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time];
135-- 2014-12-13 00:00:00.000
136
137SELECT TRY_CONVERT(datetime, '13 December 2014' ) AS [Date&Time]; -- 2014-12-13 00:00:00.000
138SELECT CONVERT(datetime2, sysdatetime()); AS [DateTime2]; -- 2016-02-12 13:09:24.0642891
139------------
140
141-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm
142DECLARE @Seconds INT
143SET @Seconds = 20000
144SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)
145/* HH MM SS
146 5 33 20 */
147------------
148-- SQL Server Date Only from DATETIME column - get date only
149-- T-SQL just date - truncate time from datetime - remove time part
150------------
151DECLARE @Now datetime = CURRENT_TIMESTAMP -- getdate()
152SELECT DateAndTime = @Now -- Date portion and Time portion
153 ,DateString = REPLACE(LEFT(CONVERT (varchar, @Now, 112),10),' ','-')
154 ,[Date] = CONVERT(DATE, @Now) -- SQL Server 2008 and on - date part
155 ,Midnight1 = dateadd(day, datediff(day,0, @Now), 0)
156 ,Midnight2 = CONVERT(DATETIME,CONVERT(int, @Now))
157 ,Midnight3 = CONVERT(DATETIME,CONVERT(BIGINT,@Now) & (POWER(Convert(bigint,2),32)-1))
158/* DateAndTime DateString Date Midnight1 Midnight2 Midnight3
1592010-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 */
160------------
161
162-- SQL Server 2008 convert datetime to date - sql yyyy mm dd
163SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),
164 Today = CONVERT(date, getdate())
165FROM AdventureWorks2008.Sales.SalesOrderHeader
166ORDER BY newid();
167/* OrderDate Today
168 2004-02-15 2012-06-18 .....*/
169------------
170
171-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd
172SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
173/* YYYY/MM/DD
174 2015/07/11 */
175SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]
176/* YYYYMMDD
177 20150711 */
178SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD]
179/* YYYY MM DD
180 2015 07 11 */
181-- Converting to special (non-standard) date fomats: DD-MMM-YY
182SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
183-- 07-MAR-14
184------------
185-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM
186PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM
187PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM
188PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM
189------------
190-- UNIX to SQL Server datetime conversion
191declare @UNIX bigint = 1477216861;
192select dateadd(ss,@UNIX,'19700101'); -- 2016-10-23 10:01:01.000
193------------
194-- String to date conversion - sql date yyyy mm dd - sql date formatting
195-- SQL Server cast string to date - sql convert date to datetime
196SELECT [Date] = CAST (@DateValue AS datetime)
197-- 2012-07-18 00:00:00.000
198
199-- SQL convert string date to different style - sql date string formatting
200SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)
201-- May 8 2014 12:00AM
202
203-- SQL Server convert date to integer
204DECLARE @Date datetime; SET @Date = getdate();
205SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);
206-- Result: 20161225
207
208-- SQL Server convert integer to datetime
209DECLARE @iDate int
210SET @iDate = 20151225
211SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)
212-- 2015-12-25 00:00:00.000
213
214-- Alternates: date-only datetime values
215-- SQL Server floor date - sql convert datetime
216SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
217SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))
218-- SQL Server cast string to datetime
219-- SQL Server datetime to string convert
220SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)
221-- SQL Server dateadd function - T-SQL datediff function
222-- SQL strip time from date - MSSQL strip time from datetime
223SELECT getdate() ,dateadd(dd, datediff(dd, 0, getdate()), 0)
224-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000
225
226-- String date - 10 bytes of storage
227SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)
228SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)
229-- Same results: 01-02-2012
230
231-- SQL Server cast datetime as string - sql datetime formatting
232SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM
233
234----------
235-- SQL date range BETWEEN operator
236----------
237-- SQL date range select - date range search - T-SQL date range query
238-- Count Sales Orders for 2003 OCT-NOV
239DECLARE @StartDate DATETIME, @EndDate DATETIME
240SET @StartDate = convert(DATETIME,'10/01/2003',101)
241SET @EndDate = convert(DATETIME,'11/30/2003',101)
242
243SELECT @StartDate, @EndDate
244-- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000
245SELECT dateadd(DAY,1,@EndDate),
246 dateadd(ms,-3,dateadd(DAY,1,@EndDate))
247-- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997
248
249-- MSSQL date range select using >= and <
250SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
251FROM Sales.SalesOrderHeader
252WHERE OrderDate >= @StartDate AND OrderDate < dateadd(DAY,1,@EndDate)
253/* Sales Orders for 2003 OCT-NOV
254 3668 */
255
256-- Equivalent date range query using BETWEEN comparison
257-- It requires a bit of trick programming
258SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
259FROM Sales.SalesOrderHeader
260WHERE OrderDate BETWEEN @StartDate AND dateadd(ms,-3,dateadd(DAY,1,@EndDate))
261-- 3668
262
263USE AdventureWorks;
264-- SQL between string dates
265SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
266WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108
267
268-- SQL BETWEEN dates without time - time stripped - time removed - date part only
269SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
270WHERE datediff(dd,0,OrderDate)
271 BETWEEN datediff(dd,0,'20040201 12:11:39') AND datediff(dd,0,'20040210 14:33:19')
272-- 108
273
274-- BETWEEN is equivalent to >=...AND....<=
275SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
276WHERE OrderDate
277BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'
278/* Orders with OrderDates
279'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)
280'2004-02-10 00:01:00.000' - 1 minute after midnight
281'2004-02-10 01:00:00.000' - 1 hour after midnight
282are not included in the two queries above. */
283-- To include the entire day of 2004-02-10 use:
284SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
285WHERE OrderDate >= '20040201' AND OrderDate < '20040211'
286
287----------
288-- Calculate week ranges in a year
289----------
290DECLARE @Year INT = '2016';
291WITH cteDays AS (SELECT DayOfYear=Dateadd(dd, number,
292 CONVERT(DATE, CONVERT(char(4),@Year)+'0101'))
293 FROM master.dbo.spt_values WHERE type='P'),
294CTE AS (SELECT DayOfYear, WeekOfYear=DATEPART(week,DayOfYear)
295 FROM cteDays WHERE YEAR(DayOfYear)= @YEAR)
296SELECT WeekOfYear, StartOfWeek=MIN(DayOfYear), EndOfWeek=MAX(DayOfYear)
297FROM CTE GROUP BY WeekOfYear ORDER BY WeekOfYear
298------------
299-- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions
300------------
301DECLARE @StringDate varchar(32)
302SET @StringDate = '2011-03-15 18:50'
303IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
304 PRINT 'VALID DATE: ' + @StringDate
305ELSE
306 PRINT 'INVALID DATE: ' + @StringDate
307GO
308-- Result: VALID DATE: 2011-03-15 18:50
309
310DECLARE @StringDate varchar(32)
311SET @StringDate = '20112-03-15 18:50'
312IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
313 PRINT 'VALID DATE: ' + @StringDate
314ELSE PRINT 'INVALID DATE: ' + @StringDate
315-- Result: INVALID DATE: 20112-03-15 18:50
316
317-- First and last day of date periods - SQL Server 2008 and on code
318DECLARE @Date DATE = '20161023'
319SELECT ReferenceDate = @Date
320SELECT FirstDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date),0))
321SELECT LastDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date)+1,-1))
322SELECT FDofSemester = CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2),0))
323SELECT LastDayOfSemester
324= CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2)+2,-1))
325SELECT FirstDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0, @Date),0))
326-- 2016-10-01
327SELECT LastDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0,@Date)+1,-1))
328-- 2016-12-31
329SELECT FirstDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date),0))
330SELECT LastDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date)+1,-1))
331SELECT FirstDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date),0))
332SELECT LastDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date)+1,-1))
333-- 2016-10-30
334
335-- Month sequence generator - sequential numbers / dates
336DECLARE @Date date = '2000-01-01'
337SELECT MonthStart=dateadd(MM, number, @Date)
338FROM master.dbo.spt_values
339WHERE type='P' AND dateadd(MM, number, @Date) <= CURRENT_TIMESTAMP
340ORDER BY MonthStart
341/* MonthStart
3422000-01-01
3432000-02-01
3442000-03-01 ....*/
345
346The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!
347
348------------
349-- Selected named date styles
350------------
351DECLARE @DateTimeValue varchar(32)
352-- US-Style
353SELECT @DateTimeValue = '10/23/2016'
354SELECT StringDate=@DateTimeValue,
355[US-Style] = CONVERT(datetime, @DatetimeValue)
356
357SELECT @DateTimeValue = '10/23/2016 23:01:05'
358SELECT StringDate = @DateTimeValue,
359[US-Style] = CONVERT(datetime, @DatetimeValue)
360
361-- UK-Style, British/French - convert string to datetime sql
362-- sql convert string to datetime
363SELECT @DateTimeValue = '23/10/16 23:01:05'
364SELECT StringDate = @DateTimeValue,
365[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
366
367SELECT @DateTimeValue = '23/10/2016 04:01 PM'
368SELECT StringDate = @DateTimeValue,
369[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
370
371-- German-Style
372SELECT @DateTimeValue = '23.10.16 23:01:05'
373SELECT StringDate = @DateTimeValue,
374[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
375
376SELECT @DateTimeValue = '23.10.2016 04:01 PM'
377SELECT StringDate = @DateTimeValue,
378[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
379------------
380
381-- Double conversion to US-Style 107 with century: Oct 23, 2016
382SET @DateTimeValue='10/23/16'
383SELECT StringDate=@DateTimeValue,
384[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
385
386-- Using DATEFORMAT - UK-Style - SQL dateformat
387SET @DateTimeValue='23/10/16'
388SET DATEFORMAT dmy
389SELECT StringDate=@DateTimeValue,
390[Date Time] = CONVERT(datetime, @DatetimeValue)
391-- Using DATEFORMAT - US-Style
392SET DATEFORMAT mdy
393-- Finding out date format for a session
394SELECT session_id, date_format from sys.dm_exec_sessions
395------------
396
397 -- Convert date string from DD/MM/YYYY UK format to MM/DD/YYYY US format
398DECLARE @UKdate char(10) = '15/03/2016'
399SELECT CONVERT(CHAR(10), CONVERT(datetime, @UKdate,103),101)
400-- 03/15/2016
401
402-- DATEPART datetime function example - SQL Server datetime functions
403SELECT * FROM Northwind.dbo.Orders
404WHERE DATEPART(YEAR, OrderDate) = '1996' AND
405 DATEPART(MONTH,OrderDate) = '07' AND
406 DATEPART(DAY, OrderDate) = '10'
407
408-- Alternate syntax for DATEPART example
409SELECT * FROM Northwind.dbo.Orders
410WHERE YEAR(OrderDate) = '1996' AND
411 MONTH(OrderDate) = '07' AND
412 DAY(OrderDate) = '10'
413------------
414-- T-SQL calculate the number of business days function / UDF - exclude SAT & SUN
415------------
416CREATE FUNCTION fnBusinessDays (@StartDate DATETIME, @EndDate DATETIME)
417RETURNS INT AS
418 BEGIN
419 IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)
420 DECLARE @i INT = 0;
421 WHILE (@StartDate <= @EndDate)
422 BEGIN
423 SET @i = @i + CASE
424 WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1
425 ELSE 0
426 END
427 SET @StartDate = @StartDate + 1
428 END -- while
429 RETURN (@i)
430 END -- function
431GO
432SELECT dbo.fnBusinessDays('2016-01-01','2016-12-31')
433-- 261
434------------
435
436-- T-SQL DATENAME function usage for weekdays
437SELECT DayName=DATENAME(weekday, OrderDate), SalesPerWeekDay = COUNT(*)
438FROM AdventureWorks2008.Sales.SalesOrderHeader
439GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)
440ORDER BY DATEPART(weekday,OrderDate)
441/* DayName SalesPerWeekDay
442Sunday 4482
443Monday 4591
444Tuesday 4346.... */
445
446-- DATENAME application for months
447SELECT MonthName=DATENAME(month, OrderDate), SalesPerMonth = COUNT(*)
448FROM AdventureWorks2008.Sales.SalesOrderHeader
449GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate) ORDER BY MONTH(OrderDate)
450/* MonthName SalesPerMonth
451January 2483
452February 2686
453March 2750
454April 2740.... */
455
456-- Getting month name from month number
457SELECT DATENAME(MM,dateadd(MM,7,-1)) -- July
458
459 ARTICLE - Essential SQL Server Date, Time and DateTime Functions
460 ARTICLE - Demystifying the SQL Server DATETIME Datatype
461------------
462-- Extract string date from text with PATINDEX pattern matching
463-- Apply sql server string to date conversion
464------------
465USE tempdb;
466go
467CREATE TABLE InsiderTransaction (
468 InsiderTransactionID int identity primary key,
469 TradeDate datetime,
470 TradeMsg varchar(256),
471 ModifiedDate datetime default (getdate()))
472-- Populate table with dummy data
473INSERT InsiderTransaction (TradeMsg) VALUES(
474'INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10')
475INSERT InsiderTransaction (TradeMsg) VALUES(
476'INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70')
477INSERT InsiderTransaction (TradeMsg) VALUES(
478'INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59')
479INSERT InsiderTransaction (TradeMsg) VALUES(
480'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Sell 5,648 8.49')
481INSERT InsiderTransaction (TradeMsg) VALUES(
482'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Option Execute 5,648 2.15')
483INSERT InsiderTransaction (TradeMsg) VALUES(
484'INSIDER TRAN QABC Hammer, Bruce D. CSO 07-31-08 Buy 5,000 8.05')
485INSERT InsiderTransaction (TradeMsg) VALUES(
486'INSIDER TRAN QABC Lennot, Mark B. Director 08-31-07 Buy 1,500 9.97')
487INSERT InsiderTransaction (TradeMsg) VALUES(
488'INSIDER TRAN QABC O''Neal, Linda COO 08-01-08 Sell 5,000 6.50')
489
490-- Extract dates from stock trade message text
491-- Pattern match for MM-DD-YY using the PATINDEX string function
492SELECT TradeDate=substring(TradeMsg,
493 patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8)
494FROM InsiderTransaction
495WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
496/* Partial results
497TradeDate
49809-02-08
49908-25-08
50008-20-08 */
501
502-- Update table with extracted date
503-- Convert string date to datetime
504UPDATE InsiderTransaction
505SET TradeDate = convert(datetime, substring(TradeMsg,
506 patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8))
507WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
508
509SELECT * FROM InsiderTransaction ORDER BY TradeDate desc
510/* Partial results
511InsiderTransactionID TradeDate TradeMsg ModifiedDate
5121 2008-09-02 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10 2008-12-22 20:25:19.263
5132 2008-08-25 00:00:00.000 INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70 2008-12-22 20:25:19.263 */
514-- Cleanup task
515DROP TABLE InsiderTransaction
516
517/************
518VALID DATE RANGES FOR DATE / DATETIME DATA TYPES
519
520DATE (3 bytes) date range:
521January 1, 1 A.D. through December 31, 9999 A.D.
522
523SMALLDATETIME (4 bytes) date range:
524January 1, 1900 through June 6, 2079
525
526DATETIME (8 bytes) date range:
527January 1, 1753 through December 31, 9999
528
529DATETIME2 (6-8 bytes) date range:
530January 1, 1 A.D. through December 31, 9999 A.D.
531
532-- The statement below will give a date range error
533SELECT CONVERT(smalldatetime, '2110-01-01')
534/* Msg 242, Level 16, State 3, Line 1
535The conversion of a varchar data type to a smalldatetime data type
536resulted in an out-of-range value. */
537************/
538The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!
539
540------------
541-- SQL CONVERT DATE/DATETIME script applying table variable
542------------
543-- SQL Server convert date
544-- Datetime column is converted into date only string column
545DECLARE @sqlConvertDate TABLE ( DatetimeColumn datetime,
546 DateColumn char(10));
547INSERT @sqlConvertDate (DatetimeColumn) SELECT GETDATE()
548
549UPDATE @sqlConvertDate
550SET DateColumn = CONVERT(char(10), DatetimeColumn, 111)
551SELECT * FROM @sqlConvertDate
552
553-- SQL Server convert datetime - String date column converted into datetime column
554UPDATE @sqlConvertDate
555SET DatetimeColumn = CONVERT(Datetime, DateColumn, 111)
556SELECT * FROM @sqlConvertDate
557
558-- Equivalent formulation - SQL Server cast datetime
559UPDATE @sqlConvertDate
560SET DatetimeColumn = CAST(DateColumn AS datetime)
561SELECT * FROM @sqlConvertDate
562/* First results
563DatetimeColumn DateColumn
5642012-12-25 15:54:10.363 2012/12/25 */
565/* Second results:
566DatetimeColumn DateColumn
5672012-12-25 00:00:00.000 2012/12/25 */
568------------
569
570-- SQL date sequence generation with dateadd & table variable
571-- SQL Server cast datetime to string - SQL Server insert default values method
572DECLARE @Sequence table (Sequence int identity(1,1))
573DECLARE @i int; SET @i = 0
574WHILE ( @i < 500)
575BEGIN
576 INSERT @Sequence DEFAULT VALUES
577 SET @i = @i + 1
578END
579SELECT DateSequence = CAST(dateadd(day, Sequence,getdate()) AS varchar)
580FROM @Sequence
581/* Partial results:
582DateSequence
583Dec 31 2008 3:02AM
584Jan 1 2009 3:02AM
585Jan 2 2009 3:02AM
586Jan 3 2009 3:02AM
587Jan 4 2009 3:02AM */
588
589-- SETTING FIRST DAY OF WEEK TO SUNDAY
590SET DATEFIRST 7;
591SELECT @@DATEFIRST
592-- 7
593SELECT CAST('2016-10-23' AS date) AS SelectDate
594 ,DATEPART(dw, '2016-10-23') AS DayOfWeek;
595-- 2016-10-23 1
596
597------------
598-- SQL Last Week calculations
599------------
600-- SQL last Friday - Implied string to datetime conversions in dateadd & datediff
601DECLARE @BaseFriday CHAR(8), @LastFriday datetime, @LastMonday datetime
602SET @BaseFriday = '19000105'
603SELECT @LastFriday = dateadd(dd,
604 (datediff (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7, @BaseFriday)
605SELECT [Last Friday] = @LastFriday
606-- Result: 2008-12-26 00:00:00.000
607
608-- SQL last Monday (last week's Monday)
609SELECT @LastMonday=dateadd(dd,
610 (datediff (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7 - 4, @BaseFriday)
611SELECT [Last Monday]= @LastMonday
612-- Result: 2008-12-22 00:00:00.000
613
614-- SQL last week - SUN - SAT
615SELECT [Last Week] = CONVERT(varchar,dateadd(day, -1, @LastMonday), 101)+ ' - ' +
616 CONVERT(varchar,dateadd(day, 1, @LastFriday), 101)
617-- Result: 12/21/2008 - 12/27/2008
618
619-----------------
620-- Specific day calculations
621------------
622-- First day of current month
623SELECT dateadd(month, datediff(month, 0, getdate()), 0)
624 -- 15th day of current month
625SELECT dateadd(day,14,dateadd(month,datediff(month,0,getdate()),0))
626-- First Monday of current month
627SELECT dateadd(day, (9-datepart(weekday,
628 dateadd(month, datediff(month, 0, getdate()), 0)))%7,
629 dateadd(month, datediff(month, 0, getdate()), 0))
630-- Next Monday calculation from the reference date which was a Monday
631DECLARE @Now datetime = GETDATE();
632DECLARE @NextMonday datetime = dateadd(dd, ((datediff(dd, '19000101', @Now)
633 / 7) * 7) + 7, '19000101');
634SELECT [Now]=@Now, [Next Monday]=@NextMonday
635-- Last Friday of current month
636SELECT dateadd(day, -7+(6-datepart(weekday,
637 dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,
638 dateadd(month, datediff(month, 0, getdate())+1, 0))
639-- First day of next month
640SELECT dateadd(month, datediff(month, 0, getdate())+1, 0)
641-- 15th of next month
642SELECT dateadd(day,14, dateadd(month, datediff(month, 0, getdate())+1, 0))
643-- First Monday of next month
644SELECT dateadd(day, (9-datepart(weekday,
645 dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,
646 dateadd(month, datediff(month, 0, getdate())+1, 0))
647
648------------
649-- SQL Last Date calculations
650------------
651-- Last day of prior month - Last day of previous month
652SELECT convert( varchar, dateadd(dd,-1,dateadd(mm, datediff(mm,0,getdate() ), 0)),101)
653-- 01/31/2019
654-- Last day of current month
655SELECT convert( varchar, dateadd(dd,-1,dateadd(mm, datediff(mm,0,getdate())+1, 0)),101)
656-- 02/28/2019
657-- Last day of prior quarter - Last day of previous quarter
658SELECT convert( varchar, dateadd(dd,-1,dateadd(qq, datediff(qq,0,getdate() ), 0)),101)
659-- 12/31/2018
660-- Last day of current quarter - Last day of current quarter
661SELECT convert( varchar, dateadd(dd,-1,dateadd(qq, datediff(qq,0,getdate())+1, 0)),101)
662-- 03/31/2019
663-- Last day of prior year - Last day of previous year
664SELECT convert( varchar, dateadd(dd,-1,dateadd(yy, datediff(yy,0,getdate() ), 0)),101)
665-- 12/31/2018
666-- Last day of current year
667SELECT convert( varchar, dateadd(dd,-1,dateadd(yy, datediff(yy,0,getdate())+1, 0)),101)
668-- 12/31/2019
669------------
670-- SQL Server dateformat and language setting
671------------
672-- T-SQL set language - String to date conversion
673SET LANGUAGE us_english
674SELECT CAST('2018-03-15' AS datetime)
675-- 2018-03-15 00:00:00.000
676
677SET LANGUAGE british
678SELECT CAST('2018-03-15' AS datetime)
679/* Msg 242, Level 16, State 3, Line 2
680The conversion of a varchar data type to a datetime data type resulted in
681an out-of-range value.
682*/
683SELECT CAST('2018-15-03' AS datetime)
684-- 2018-03-15 00:00:00.000
685
686SET LANGUAGE us_english
687
688-- SQL dateformat with language dependency
689SELECT name, alias, dateformat
690FROM sys.syslanguages
691WHERE langid in (0,1,2,4,5,6,7,10,11,13,23,31)
692GO
693/*
694name alias dateformat
695us_english English mdy
696Deutsch German dmy
697Français French dmy
698Dansk Danish dmy
699Español Spanish dmy
700Italiano Italian dmy
701Nederlands Dutch dmy
702Suomi Finnish dmy
703Svenska Swedish ymd
704magyar Hungarian ymd
705British British English dmy
706Arabic Arabic dmy */
707------------
708
709-- Generate list of months
710;WITH CTE AS (
711 SELECT 1 MonthNo, CONVERT(DATE, '19000101') MonthFirst
712 UNION ALL
713 SELECT MonthNo+1, DATEADD(Month, 1, MonthFirst)
714 FROM CTE WHERE Month(MonthFirst) < 12 )
715SELECT MonthNo AS MonthNumber, DATENAME(MONTH, MonthFirst) AS MonthName
716FROM CTE ORDER BY MonthNo
717/* MonthNumber MonthName
718 1 January
719 2 February
720 3 March ... */