SQL Server Date Format 小笔记[1]

1)Datetime Format

Data Type	Language-Neutral Formats	Examples
DATETIME	'YYYYMMDD hh:mm:ss.nnn'   'YYYY-MM-DDThh:mm:ss.nnn' 'YYYYMMDD'	'20090212 12:30:15.123'   '2009-02-12T12:30:15.123' '20090212'
SMALLDATETIME	'YYYYMMDD hh:mm'   'YYYY-MM-DDThh:mm' 'YYYYMMDD'	'20090212 12:30'   '2009-02-12T12:30' '20090212'
DATE	'YYYYMMDD'   'YYYY-MM-DD'	'20090212'   '2009-02-12'
DATETIME2	'YYYYMMDD hh:mm:ss.nnnnnnn'   'YYYY-MM-DD hh:mm:ss.nnnnnnn' 'YYYY-MM-DDThh:mm:ss.nnnnnnn' 'YYYYMMDD' 'YYYY-MM-DD'	'20090212 12:30:15.1234567'   '2009-02-12 12:30:15.1234567' '2009-02-12T12:30:15.1234567' '20090212' '2009-02-12'
DATETIMEOFFSET	'YYYYMMDD hh:mm:ss.nnnnnnn [+|-]hh:mm'   'YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm' 'YYYYMMDD' 'YYYY-MM-DD'	'20090212 12:30:15.1234567 +02:00'   '2009-02-12 12:30:15.1234567 +02:00' '20090212' '2009-02-12'
TIME	'hh:mm:ss.nnnnnnn'	'12:30:15.1234567'

2)上图使用举例

SET LANGUAGE British;SELECT CAST('20070212' AS DATETIME);

SELECT CONVERT(DATETIME, '02/12/2007', 101);

SELECT
  CAST(SYSDATETIME() AS DATE) AS [current_date],
  CAST(SYSDATETIME() AS TIME) AS [current_time];

The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone.

SWITCHOFFSET( datetimeoffset_value, time_zone )

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');

 

3)常用函数例子

The DATEADD function allows you to add a specified number of units of a specified date part to an input date and time value.

DATEADD( part, n, dt_val )

SELECT DATEADD(year, 1, '20090212');

SELECT DATEPART(month, '20090212');

SELECT YEAR( dt_val )

SELECT MONTH( dt_val )

SELECT DAY( dt_val )

SELECT DATENAME(month, '20090212');

SELECT ISDATE('20090212');

 

4)data convert 时间转化

select  CONVERT(datetime,convert(nvarchar,GETDATE(),110),110) between FromDate and ToDate

select CONVERT(date,getdate(),110) //更好的解决方案

SELECT  CAST(SYSDATETIME() AS DATE) AS [current_date] //2008 sql server