Chittibabu

MS SQL Server 2005 provides CONVERT function to change or convert the DateTime formats.



CONVERT Function syntax:

Convert (datatype, datetime string, date style)



DateType parameter accepts the following types:
nchar, nvarchar, char, varchar, binary or varbinary
DataType length is optional.



DateTime String parameter accepts the datatime variable, datetime type column name or getdate() function to convert the datetime format.



Date Style parameter accepts the style number in which you want to convert the datetime.



Following are the SQL query Syntax to convert DateTime format:



SQL query to convert datetime into mon dd yyyy hh:miAM (or PM) format:

Select convert(varchar, getdate(), 100)



SQL query to convert datetime into Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) format:

Select convert(varchar, getdate(), 109)



SQL query to convert datetime into Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) format:

Select convert(varchar, getdate(), 113)



SQL query to convert datetime into ODBC canonical yyyy-mm-dd hh:mi:ss(24h) format:

Select convert(varchar, getdate(), 120)



SQL query to convert datetime into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) format:

Select convert(varchar, getdate(), 121)



SQL query to convert datetime into ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) format:

Select convert(varchar, getdate(), 126)