Converting Datetime values to various formats - SQL

Using the CONVERT function in SQL Server 2005 the following datetime formats can be produced:


SELECT
GETDATE() AS UnconvertedDateTimeUsingGetDate,
CURRENT_TIMESTAMP AS UnconvertedDateTimeUsingCurrentTimestamp,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 100) AS [Default mon dd yyyy hh:miAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 101) AS [USA mm/dd/yy],
CONVERT(nvarchar(30), GETDATE(), 102) AS [ANSI yy.mm.dd],
CONVERT(nvarchar(30), GETDATE(), 103) AS [British/French dd/mm/yy],
CONVERT(nvarchar(30), GETDATE(), 104) AS [German dd.mm.yy],
CONVERT(nvarchar(30), GETDATE(), 105) AS [Italian dd-mm-yy],
CONVERT(nvarchar(30), GETDATE(), 106) AS [dd mon yy],
CONVERT(nvarchar(30), GETDATE(), 107) AS [Mon dd, yy],
CONVERT(nvarchar(30), GETDATE(), 108) AS [hh:mm:ss],
CONVERT(nvarchar(30), GETDATE(), 109) AS [Default+milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 110) AS [USA mm-dd-yy],
CONVERT(nvarchar(30), GETDATE(), 111) AS [Japan yy/mm/dd],
CONVERT(nvarchar(30), GETDATE(), 112) AS [ISO yymmdd],
CONVERT(nvarchar(30), GETDATE(), 113) AS [Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 114) AS [hh:mi:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 120) AS [ODBC canonical yyyy-mm-dd hh:mi:ss(24h)],
CONVERT(nvarchar(30), GETDATE(), 121) AS [ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 126) AS [ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)],
CONVERT(nvarchar(30), GETDATE(), 130) AS [Hijri**** dd mon yyyy hh:mi:ss:mmmAM],
CONVERT(nvarchar(30), GETDATE(), 131) AS [ Hijri**** dd/mm/yy hh:mi:ss:mmmAM]

Useful date formulas - SQL

This was posted on http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

declare @ThisDate datetime;

set @ThisDate = getdate();

select dateadd(dd, datediff(dd, 0, @ThisDate), 0) -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0) -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0) -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year

Datetime types in SQL - SQL

You cannot store only the date or time portion.

If only the date is specified, 00:00:00.000 is stored in the time portion.

If only the time is specified, 1900-01-01 is stored in the date portion.

For example,

SELECT CAST ('20100526' as datetime)

SELECT CAST ('12:47:59:009' as datetime)