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
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
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
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.
SELECT YEAR(0)
1900
select MONTH(GETDATE())
SELECT MONTH(0)
1
SELECT DATEPART (WEEK, '2015-01-07')
2
SELECT DAY('2007-04-30');
30
SELECT MONTH(0)
1
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
SQL Server supports two date string format. The ISO 8601 and it's own date format.
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:
select convert(datetime,'2004-05-23T14:25:10') as datetime
SET DATEFORMAT { format | @format_var }
where:
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