D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Tuesday, 30 November 2010

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]

No comments: