Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Query Editor to demonstrate the use of the SQL datetime formatting and management functions.
USE AdventureWorks2008;
------------
-- SQL Server 2008 string to datetime conversion script generator
------------
-- SQL string to datetime - t-sql varchar to datetime - sql convert
-- Generated results - generator script follows
SELECT convert(datetime, 'Jul 16 2015 3:03AM', 0)
SELECT convert(datetime, '07/16/15', 1)
SELECT convert(datetime, '15.07.16', 2)
SELECT convert(datetime, '16/07/15', 3)
SELECT convert(datetime, '16.07.15', 4)
SELECT convert(datetime, '16-07-15', 5)
SELECT convert(datetime, '16 Jul 15', 6)
SELECT convert(datetime, 'Jul 16, 15', 7)
SELECT convert(datetime, '03:03:35', 8)
SELECT convert(datetime, 'Jul 16 2015 3:03:35:260AM', 9)
SELECT convert(datetime, '07-16-15', 10)
SELECT convert(datetime, '15/07/16', 11)
SELECT convert(datetime, '150716', 12)
SELECT convert(datetime, '16 Jul 2015 03:03:35:260', 13)
SELECT convert(datetime, '03:03:35:260', 14)
SELECT convert(datetime, '2015-07-16 03:03:35', 20)
SELECT convert(datetime, '2015-07-16 03:03:35.260', 21)
SELECT convert(datetime, '03:03:35', 24)
SELECT convert(datetime, 'Jul 16 2015 3:03AM', 100)
SELECT convert(datetime, '07/16/2015', 101)
SELECT convert(datetime, '2015.07.16', 102)
SELECT convert(datetime, '16/07/2015', 103)
SELECT convert(datetime, '16.07.2015', 104)
SELECT convert(datetime, '16-07-2015', 105)
SELECT convert(datetime, '16 Jul 2015', 106)
SELECT convert(datetime, 'Jul 16, 2015', 107)
SELECT convert(datetime, '03:03:35', 108)
SELECT convert(datetime, 'Jul 16 2015 3:03:35:270AM', 109)
SELECT convert(datetime, '07-16-2015', 110)
SELECT convert(datetime, '2015/07/16', 111)
SELECT convert(datetime, '20150716', 112)
SELECT convert(datetime, '16 Jul 2015 03:03:35:270', 113)
SELECT convert(datetime, '03:03:35:270', 114)
SELECT convert(datetime, '2015-07-16 03:03:35', 120)
SELECT convert(datetime, '2015-07-16 03:03:35.270', 121)
SELECT convert(datetime, '2015-07-16T03:03:35.270', 126)
SELECT convert(datetime, '2015-07-16T03:03:35.270', 127)
SELECT convert(datetime, '24/07/1430 3:03:35:270AM', 131)
------------
-- T SQL GENERATOR SCRIPT - while loop - try-catch error control
DECLARE @i int = -1, @string varchar(128)
WHILE ( @i < 150 )
BEGIN
SET @i+=1
IF @i in (22, 23, 25, 130) continue; -- exceptions do not work
BEGIN TRY
SELECT @string= 'SELECT convert(datetime, '''+
convert(varchar,getdate(),@i)+''', '+convert(varchar,@i)+')'
PRINT @string
END TRY
BEGIN CATCH
END CATCH
END -- while
GO
------------
------------
-- Generator script for all datetime conversions to string
------------
DECLARE @I INT = -1
DECLARE @SQLDynamic NVARCHAR(1024)
CREATE TABLE #SQL (
StyleID INT,
SQL VARCHAR(256),
Result VARCHAR(32));
WHILE (@I < 127)
BEGIN
SET @I += 1
IF @I > 14 AND @I < 20 CONTINUE -- unused style number ranges
IF @I > 25 AND @I < 100 CONTINUE
IF @I > 114 AND @I < 120 CONTINUE
IF @I > 121 AND @I < 126 CONTINUE
INSERT #SQL (StyleID, SQL)
SELECT @I, 'SELECT ' + 'CONVERT(VARCHAR, GETDATE(), ' +
CONVERT(VARCHAR,@I) + ')'
SET @SQLDynamic = 'UPDATE #SQL SET Result=(SELECT CONVERT(VARCHAR,
GETDATE(), ' + CONVERT(VARCHAR,@I) + ')) WHERE StyleID=' +
CONVERT(VARCHAR,@I)
PRINT @SQLDynamic -- test & debug
EXEC sp_executeSQL @SQLDynamic -- Dynamic SQL execution
END
SELECT * FROM #SQL
GO
DROP TABLE #SQL
/*
StyleID SQL Result
0 SELECT CONVERT(VARCHAR, GETDATE(), 0) Jul 25 2016 8:49AM
1 SELECT CONVERT(VARCHAR, GETDATE(), 1) 07/25/16
2 SELECT CONVERT(VARCHAR, GETDATE(), 2) 16.07.25
3 SELECT CONVERT(VARCHAR, GETDATE(), 3) 25/07/16
4 SELECT CONVERT(VARCHAR, GETDATE(), 4) 25.07.16
5 SELECT CONVERT(VARCHAR, GETDATE(), 5) 25-07-16
6 SELECT CONVERT(VARCHAR, GETDATE(), 6) 25 Jul 16
7 SELECT CONVERT(VARCHAR, GETDATE(), 7) Jul 25, 16
8 SELECT CONVERT(VARCHAR, GETDATE(), 8) 08:49:52
9 SELECT CONVERT(VARCHAR, GETDATE(), 9) Jul 25 2016 8:49:52:713AM
10 SELECT CONVERT(VARCHAR, GETDATE(), 10) 07-25-16
11 SELECT CONVERT(VARCHAR, GETDATE(), 11) 16/07/25
12 SELECT CONVERT(VARCHAR, GETDATE(), 12) 160725
13 SELECT CONVERT(VARCHAR, GETDATE(), 13) 25 Jul 2016 08:49:52:720
14 SELECT CONVERT(VARCHAR, GETDATE(), 14) 08:49:52:723
20 SELECT CONVERT(VARCHAR, GETDATE(), 20) 2016-07-25 08:49:52
21 SELECT CONVERT(VARCHAR, GETDATE(), 21) 2016-07-25 08:49:52.727
22 SELECT CONVERT(VARCHAR, GETDATE(), 22) 07/25/16 8:49:52 AM
23 SELECT CONVERT(VARCHAR, GETDATE(), 23) 2016-07-25
24 SELECT CONVERT(VARCHAR, GETDATE(), 24) 08:49:52
25 SELECT CONVERT(VARCHAR, GETDATE(), 25) 2016-07-25 08:49:52.737
100 SELECT CONVERT(VARCHAR, GETDATE(), 100) Jul 25 2016 8:49AM
101 SELECT CONVERT(VARCHAR, GETDATE(), 101) 07/25/2016
102 SELECT CONVERT(VARCHAR, GETDATE(), 102) 2016.07.25
103 SELECT CONVERT(VARCHAR, GETDATE(), 103) 25/07/2016
104 SELECT CONVERT(VARCHAR, GETDATE(), 104) 25.07.2016
105 SELECT CONVERT(VARCHAR, GETDATE(), 105) 25-07-2016
106 SELECT CONVERT(VARCHAR, GETDATE(), 106) 25 Jul 2016
107 SELECT CONVERT(VARCHAR, GETDATE(), 107) Jul 25, 2016
108 SELECT CONVERT(VARCHAR, GETDATE(), 108) 08:49:52
109 SELECT CONVERT(VARCHAR, GETDATE(), 109) Jul 25 2016 8:49:52:823AM
110 SELECT CONVERT(VARCHAR, GETDATE(), 110) 07-25-2016
111 SELECT CONVERT(VARCHAR, GETDATE(), 111) 2016/07/25
112 SELECT CONVERT(VARCHAR, GETDATE(), 112) 20160725
113 SELECT CONVERT(VARCHAR, GETDATE(), 113) 25 Jul 2016 08:49:52:833
114 SELECT CONVERT(VARCHAR, GETDATE(), 114) 08:49:52:833
120 SELECT CONVERT(VARCHAR, GETDATE(), 120) 2016-07-25 08:49:52
121 SELECT CONVERT(VARCHAR, GETDATE(), 121) 2016-07-25 08:49:52.840
126 SELECT CONVERT(VARCHAR, GETDATE(), 126) 2016-07-25T08:49:52.843
127 SELECT CONVERT(VARCHAR, GETDATE(), 127) 2016-07-25T08:49:52.847
*/
------------
------------
-- SQL GROUP BY half an hour - sql server order by half an hour
------------
-- SQL Server DATEADD, DATEDIFF, CHECKSUM, CONVERT functions
USE tempdb;
SELECT SalesOrderID,
TotalDue,
OrderDate = DATEADD(MINUTE,CHECKSUM(SalesOrderID),OrderDate)
INTO SOH -- Generate test data with SELECT INTO table create
FROM AdventureWorks2008.Sales.SalesOrderHeader
-- SQL Server group by 15 minutes - change value next line to 15
DECLARE @IntervalMinutes INT = 30
-- MSSQL group by half an hour time only without date
SELECT Period = Convert(VARCHAR,DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',
OrderDate) / @IntervalMinutes) * @IntervalMinutes,
'19000101'),108),
Total = SUM(TotalDue)
FROM SOH
GROUP BY Convert(VARCHAR,DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',
OrderDate) / @IntervalMinutes) * @IntervalMinutes,
'19000101'),108)
ORDER BY Period;
/* Partial results:
Period Total
00:00:00 3260245.8458
00:30:00 1051295.5751
01:00:00 801851.0782
01:30:00 1907088.0003
02:00:00 3021316.4375
*/
-- T-SQL group by half an hour for each order date
SELECT Period = DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',
OrderDate) / @IntervalMinutes) * @IntervalMinutes,
'19000101'),
Total = SUM(TotalDue)
FROM SOH
GROUP BY DATEADD(MINUTE,(DATEDIFF(MINUTE,'19000101',
OrderDate) / @IntervalMinutes) * @IntervalMinutes,
'19000101')
ORDER BY Period;
GO
/* Partial results
Period Total
2004-06-06 15:30:00.000 11006.8392
2004-06-06 16:00:00.000 24621.5335
2004-06-06 16:30:00.000 20454.158
*/
DROP TABLE tempdb.dbo.SOH
------------
-- Selecting with CONVERT into different styles
-- Note: Only Japan & ISO styles can be used in ORDER BY for sorting
SELECT DISTINCT TOP(3)
Italy = CONVERT(char(10), OrderDate, 105)
, USA = CONVERT(char(10), OrderDate, 110)
, Japan = CONVERT(char(10), OrderDate, 111)
, ISO = CONVERT(char(8), OrderDate, 112)
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
/* Results
Italy USA Japan ISO
17-05-2001 05-17-2001 2001/05/17 20010517
31-05-2001 05-31-2001 2001/05/31 20010531
14-01-2002 01-14-2002 2002/01/14 20020114
*/
/***** SELECTED SQL DATETIME FORMATS WITH NAMES *****/
-- US-Style format: 10/23/2006
select [US-Style]=CONVERT(varchar,GETDATE(),101)
-- UK-Style (British/French) format: 23/10/2006
select [UK-Style]=CONVERT(varchar,GETDATE(),103)
-- German format: 23.10.2006
select [German]=CONVERT(varchar,GETDATE(),104)
-- ISO format: 20061023
select ISO=convert(varchar,GETDATE(),112)
-- ISO8601 format: 2008-10-23T19:20:16.003
select [ISO8601]=convert(varchar,GETDATE(),126)
------------
-- SQL Date and Time Functions
------------
-- SQL CURRENT_TIMESTAMP function
-- local NYC - EST - Eastern Standard Time zone
SELECT CURRENT_TIMESTAMP -- 2014-01-05 07:02:10.577
-- SQL DATEADD Date and Time Function
SELECT DATEADD(month,2,'2014-12-09') -- 2015-02-09 00:00:00.000
-- SQL DATEDIFF Date and Time Function
SELECT DATEDIFF(day,'2014-12-09','2015-02-09') -- 62
-- SQL DATENAME Date and Time Function
SELECT DATENAME(month, '2014-12-09') -- December
SELECT DATENAME(weekday, '2014-12-09') -- Sunday
-- SQL DATEPART Date and Time Function
SELECT DATEPART(month, '2014-12-09') -- 12
-- SQL DAY Date and Time Function
SELECT DAY('2014-12-09') -- 9
-- SQL GETDATE Date and Time Function
-- local NYC - EST - Eastern Standard Time zone
SELECT GETDATE() -- 2014-01-05 07:02:10.577
-- SQL GETUTCDATE Date and Time Function
-- London - Greenwich Mean Time
SELECT GETUTCDATE() -- 2014-01-05 12:02:10.577
-- SQL ISDATE Date and Time Function
SELECT ISDATE('2014-12-09') -- 1
SELECT ISDATE('2014-18-09') -- 0
-- SQL MONTH Date and Time Function
SELECT MONTH('2014-12-09') -- 12
-- SQL SYSDATETIME() Date and Time Function
SELECT SYSDATETIME() -- 2014-01-05 07:02:10.5777500
-- SQL SYSUTCDATETIME() Date and Time Function
SELECT SYSUTCDATETIME() -- 2014-01-05 12:02:10.5777500
-- SQL YEAR Date and Time Function
SELECT YEAR('2014-12-09') -- 2014
------------
------------
-- Application examples for Date and Time Functions
------------
-- DATEADD: ADD 7 DAYS
SELECT DATEADD(DD, 7, GETDATE())
SELECT DATEADD(WW, 1, GETDATE()
-- DAY NUMBER OF THE CURRENT DATE
SELECT DAY (GETDATE())
-- BEGINNING DATE FOR CURRENT MONTH
SELECT DATEADD(DD,-(DAY(GETDATE())-1),CONVERT(VARCHAR,GETDATE(),112))
-- ENDING DATE FOR CURRENT MONTH
SELECT DATEADD(DD,-(DAY(DATEADD(MM,1,GETDATE()))),DATEADD(MM,1,
CONVERT(VARCHAR,GETDATE(),112)))
-- DATEDIFF: NUMBER OF DAYS SINCE JAN. 1, 2007
SELECT DATEDIFF(DD,'20070101',GETDATE())
-- DATENAME: CURRENT MONTH
SELECT DATENAME(MM, GETDATE())
-- MONTH NUMBER OF THE CURRENT DATE
SELECT MONTH(GETDATE())
-- YEAR NUMBER OF THE CURRENT DATE
SELECT YEAR(CURRENT_TIMESTAMP)
-- T-SQL DATENAME function usage for weekdays
SELECT DayName=DATENAME(weekday, OrderDate), PurchasesPerWeekDay = COUNT(*)
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)
ORDER BY DATEPART(weekday,OrderDate)
/* DayName PurchasesPerWeekDay
Sunday 580
Monday 841
Tuesday 772
Wednesday 692
Thursday 168
Friday 233
Saturday 726*/
-- DATENAME application for months
SELECT MonthName=DATENAME(month, OrderDate), PurchasesPerMonth = COUNT(*)
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate)
ORDER BY MONTH(OrderDate)
/* MonthName PurchasesPerMonth
January 280
February 314
March 353
April 333
May 417
June 405
July 438
August 452
September 412
October 261
November 116
December 231 */
------------
-- Customized date & time conversions
------------
SELECT CurrentDate=rtrim(year(getdate())) + '\' +
right('0' + rtrim(month(getdate())),2) + '\' +
right('0' + rtrim(day(getdate())),2)
-- Datetime composition from date parts
DECLARE @Year int = 2010, @Month int = 3, @Day int = 15
SELECT dateadd(dd, @Day-1, dateadd(mm, @Month-1,
dateadd(yy, (@Year-1900),0)))
-- Result: 2010-03-15 00:00:00.000
-- Converting seconds to HH:MM:SS format
DECLARE @Seconds int = 20000
SELECT TimeSpan=right('0' +rtrim(@Seconds / 3600),2) + ':' +
right('0' + rtrim((@Seconds % 3600) / 60),2) + ':' +
right('0' + rtrim(@Seconds % 60),2)
-- Result: 05:33:20
-- Test result
SELECT 5*3600 + 33*60 + 20
-- Result: 20000
-- DATEPART datetime function example
SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE
DATEPART(YEAR, OrderDate) = 2003 AND
DATEPART(MONTH, OrderDate) = 7 AND
DATEPART(DAY, OrderDate) = 10
-- Alternate syntax examples
SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE
YEAR(OrderDate) = 2003 AND
MONTH(OrderDate) = 7 AND
DAY(OrderDate) = 10
SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE
YEAR(OrderDate) = '2003' AND
MONTH(OrderDate) = '07' AND
DAY(OrderDate) = '10'
-- SQL date composition
-- SQL datetime to string
-- SQL year, month & day datetime functions
DECLARE @Date datetime = CURRENT_TIMESTAMP;
-- SQL datetime to string
-- SQL date DD.MM.YYYY format
SELECT RIGHT('0' + RTRIM(DAY(@Date)),2) + '.' +
RIGHT('0' + RTRIM(MONTH(@Date)),2) + '.' +
RTRIM(YEAR(@Date))
-- Results: 10.01.2012
-- Set the time portion of a datetime value to 00:00:00.000
DECLARE @DateTime datetime = getdate()
SELECT @DateTime, dateadd(dd,0, datediff(dd,0,@DateTime))
--Results: 2011-12-25 08:21:24.733 2011-12-25 00:00:00.000
----------
-- SQL DATEPART
----------
SELECT [Date Part]=DATEPART(Year, getdate())
-- 2012
SELECT [Date Part]=DATEPART(yy, getdate())
SELECT [Date Part]=DATEPART(yyyy, getdate())
SELECT [Date Part]=DATEPART(quarter, getdate())
-- 1
SELECT [Date Part]=DATEPART(qq, getdate())
SELECT [Date Part]=DATEPART(q, getdate())
SELECT [Date Part]=DATEPART(month, getdate())
-- 12
SELECT [Date Part]=DATEPART(mm, getdate())
SELECT [Date Part]=DATEPART(m, getdate())
-- SQL Julian date
SELECT [Date Part]=DATEPART(dayofyear, getdate())
-- 335
SELECT [Date Part]=DATEPART(dy, getdate())
SELECT [Date Part]=DATEPART(y, getdate())
-- SQL day of month
SELECT [Date Part]=DATEPART(day, getdate())
-- 28
SELECT [Date Part]=DATEPART(dd, getdate())
SELECT [Date Part]=DATEPART(d, getdate())
SELECT [Date Part]=DATEPART(week, getdate())
-- 40
SELECT [Date Part]=DATEPART(wk, getdate())
SELECT [Date Part]=DATEPART(ww, getdate())
-- SQL day of week
SELECT [Date Part]=DATEPART(weekday, getdate())
-- 6
SELECT [Date Part]=DATEPART(dw, getdate())
SELECT [Date Part]=DATEPART(hour, getdate())
-- 5
SELECT [Date Part]=DATEPART(hh, getdate())
SELECT [Date Part]=DATEPART(minute, getdate())
-- 44
SELECT [Date Part]=DATEPART(mi, getdate())
SELECT [Date Part]=DATEPART(n, getdate())
SELECT [Date Part]=DATEPART(second, getdate())
-- 30
SELECT [Date Part]=DATEPART(ss, getdate())
SELECT [Date Part]=DATEPART(s, getdate())
SELECT [Date Part]=DATEPART(millisecond, getdate())
-- 590
SELECT [Date Part]=DATEPART(ms, getdate())
----------
----------
-- SQL DATENAME
----------
SELECT [Date Name]=DATENAME(Year, getdate())
-- 2012
SELECT [Date Name]=DATENAME(yy, getdate())
SELECT [Date Name]=DATENAME(yyyy, getdate())
SELECT [Date Name]=DATENAME(quarter, getdate())
-- 4
SELECT [Date Name]=DATENAME(qq, getdate())
SELECT [Date Name]=DATENAME(q, getdate())
-- SQL name of month
SELECT [Date Name]=DATENAME(month, getdate())
-- January
SELECT [Date Name]=DATENAME(mm, getdate())
SELECT [Date Name]=DATENAME(m, getdate())
-- SQL Julian date
SELECT [Date Name]=DATENAME(dayofyear, getdate())
-- 235
SELECT [Date Name]=DATENAME(dy, getdate())
SELECT [Date Name]=DATENAME(y, getdate())
-- SQL day of month
SELECT [Date Name]=DATENAME(day, getdate())
-- 18
SELECT [Date Name]=DATENAME(dd, getdate())
SELECT [Date Name]=DATENAME(d, getdate())
SELECT [Date Name]=DATENAME(week, getdate())
-- 30
SELECT [Date Name]=DATENAME(wk, getdate())
SELECT [Date Name]=DATENAME(ww, getdate())
-- SQL name of day
SELECT [Date Name]=DATENAME(weekday, getdate())
-- Saturday
SELECT [Date Name]=DATENAME(dw, getdate())
SELECT [Date Name]=DATENAME(hour, getdate())
-- 5
SELECT [Date Name]=DATENAME(hh, getdate())
SELECT [Date Name]=DATENAME(minute, getdate())
-- 44
SELECT [Date Name]=DATENAME(mi, getdate())
SELECT [Date Name]=DATENAME(n, getdate())
SELECT [Date Name]=DATENAME(second, getdate())
-- 30
SELECT [Date Name]=DATENAME(ss, getdate())
SELECT [Date Name]=DATENAME(s, getdate())
SELECT [Date Name]=DATENAME(millisecond, getdate())
-- 700
SELECT [Date Name]=DATENAME(ms, getdate())
----------
-------------
-- SQL Server 2008 Combine DATE and TIME(7) into DATETIME2(7)
-------------
-- SQL concatenate date and time
USE AdventureWorks2008;
DECLARE @Date DATE,
@Time7 TIME(7),
@Date7 DATETIME2(7)
SELECT @Date = CONVERT(VARCHAR,GETDATE(),112),
@Time7 = '20:30:40.9876543'
-- Map to 1900-01-01
SET @Date7 = @Time7
SELECT Date7=@Date7, [Date]=@Date,
Combination=DATEAdd(DAY,datediff(DAY,@Date7,@Date),@Date7)
/*
Date7 Date Combination
1900-01-01 20:30:40.9876543 2016-07-31 2016-07-31 20:30:40.9876543
*/
------------
-- SQL Server 2008 T-SQL find next Monday for a given date
DECLARE @Date DATE = '2014-12-31'
SELECT NextMondaysDate=CONVERT(DATE, DATEADD(dd, (DATEDIFF(dd, 0, @Date)
/ 7 * 7) + 7, 0)),
WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @Date)/7*7)+7, 0));
/*
NextMondaysDate WeekDayName
2015-01-05 Monday
*/
------------
No comments:
Post a Comment