Initialization
SQL
The cast, convert and string_format conversion permits to initialize a date.
select
convert(date,'2012-10-25') as date,
convert(datetime,'2004-05-23T14:25:10') as 'datetime iso', -- ISO 8601
convert(datetime,'20040523 14:25:10') as 'datetime unseparated', -- Unseparated YYYYMMDD hh:mm:ss[.mmm]
CAST('2007-05-08 12:35:29.123' as datetime) as 'datetime cast',
convert(datetime2,'9999-12-31 23:59:59.9999999') as datetime2
TSQL
DECLARE @date date= '2012-10-25'; -- of 12-10-25
DECLARE @datetime datetime= @date;
SELECT @date AS '@date', @datetime AS '@datetime';
@date @datetime
2012-10-25 2012-10-25 00:00:00.000
Functions
Current Date
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
Date Part
The values that are returned for DATEPART (year, date), DATEPART (month, date), and DATEPART (day, date) are the same as those returned by the functions YEAR, MONTH, and DAY, f respectively.
Year
SELECT YEAR(0)
1900
Month
select MONTH(GETDATE())
SELECT MONTH(0)
1
Week
SELECT DATEPART (WEEK, '2015-01-07')
2
Day
SELECT DAY('2007-04-30');
30
SELECT MONTH(0)
1
(CAST|CONVERT)
The CONVERT function can give you the data in different style :
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD] -- ISO
Configuration
String Format
SQL Server supports two date string format. The ISO 8601 and it's own date format.
ISO 8601
The ISO 8601 format is not affected by the dateformat and is a standard.
To use the ISO 8601 format, you must specify each element in the format. This includes:
- the T,
- the colons (:),
- and the period (.)
select convert(datetime,'2004-05-23T14:25:10') as datetime
DATEFORMAT
SET DATEFORMAT { format | @format_var }
where:
- format is the order of the date parts. Valid parameters are mdy, dmy, ymd, ydm, myd, and dym.
SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE. When the language is set to us_english, the default order for the date is mdy
The DATEFORMAT ydm is not supported for date, datetime2 and datetimeoffset data types.
Example:
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
2008-12-31 09:01:01.123