SQL Server 2005 Convert DateTime Format

Updated on 09 Jan 2012,
Published on 21 Feb 2008

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:

1. nchar, nvarchar, char, varchar, binary or varbinary. DataType length is optional.

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

3. 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:

Convert Datetime into mon dd yyyy hh:miAM (or PM) format

SELECT CONVERT(varchar, getdate(), 100)

Convert Datetime into Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) format

SELECT CONVERT(varchar, getdate(), 109)

Convert Datetime into Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) format

SELECT CONVERT(varchar, getdate(), 113)

Convert Datetime into ODBC canonical yyyy-mm-dd hh:mi:ss(24h) format

SELECT CONVERT(varchar, getdate(), 120)

Convert Datetime into ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) format

SELECT CONVERT(varchar, getdate(), 121)

Convert Datetime into ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces) format

SELECT CONVERT(varchar, getdate(), 126)

Continue to next tutorial: SQL Server 2005 Convert Date Format to learn how to convert the date values to different types of formats.

Previous Tutorial

Next Tutorial

sponsored links

17 Responses to "SQL Server 2005 Convert DateTime Format"

Siva

Nice Explanation about the datetime formates..

shrikant

I m trying to convert date, but when i pass date in following it display no change in date. My passed date is, select convert(varchar,'12/08/2009 12:00:00 PM',100)

Ezineasp.net

Hi Shrikant

You are getting this problem coz date format you are passing to the convert function is of string type.
You must cast the string type value to datetime data type. Try the following SQL code:
select convert(varchar,cast('12/08/2009 12:00:00 PM' as datetime),100)
Also see here: SQL Server 2005 Cast-Convert Date Time Format

azarudeen.S

I use this query....

"select convert(varchar,cast('12/08/2009 12:00:00 PM' as datetime),100)"

i get t answer thanks....

Bharath

i am getting the error as "Conversion failed when converting datetime from character string." for SELECT CONVERT(VARCHAR(MAX), CAST('26-APR-07 03.06.07.000000000 PM' AS DATETIME), 121) help needed

Ezineasp.net

Hi Bharath

You can try the following SQL Code in your stored procedure if like it:

DECLARE @originalDateTimeString AS VARCHAR(50)
DECLARE @dateTimeString AS VARCHAR(26)

SET @originalDateTimeString = '26-APR-07 03.06.07.000000000 PM'
SET @dateTimeString = SUBSTRING(@originalDateTimeString, 0, 10) + ' '
+ REPLACE(SUBSTRING(@originalDateTimeString, 11, 8), '.', ':')
+ SUBSTRING(@originalDateTimeString, 19, 4) + ' '
+ SUBSTRING(@originalDateTimeString, LEN(@originalDateTimeString) - 1, 2)

SELECT @dateTimeString AS DateTimeStrign, CONVERT(VARCHAR(50), CAST(@dateTimeString AS DATETIME), 121) AS FormattedDateTime

Hope this will help you.

decent.. output would have bee handy thought.. thx

babakuki

Date format does not change when using the command Convert(int, t.Origin_DateKey, 106)AS datetime.
Will like to have date in dd/mm/yyyy format, please help..

Ezineasp.net

@Babakuki

Try the following method to format data as dd/mm/yyyy:

CONVERT(varchar, t.Origin_DateKey, 103)

Hope it will work for you.

Babakuki

@Ezineasp.net

Thanks for your response, your assistance is much appreciated.

Im afraid, the suggestion did not work.
Thanks

Sachin Sharma

Helloo every one i need your help....
SQL QUERY:
how we get YEAR this format : '2010-11'

help Needed.

Ezineasp.net

@Sachin

You can try the following method to format the date as YYYY-mm-dd:

select convert(char(10), getdate(), 121)

Good Luck ;)

sushmita

@Ezineasp.net

pls can you check this eg.
i have a field name net_date which is a char datatype and has values such as 062011,072011 and so on..
SELECT net_date,datename(month,cast(net_date as int))as mnthname from Netting_Calndr

it gives the output adding the value of the field viz 06+2+0+1+1=10 so october as mnthname

and
SELECT net_date,datename(month,cast(substring(net_date,1,2) as int)) from Netting_Calndr
it gives output as all the month as january..
can you pls tell me what will be the correct code..
can you pls help..asap..

regards
Sushmita

Ezineasp.net

@Sushmita You can try the following code: SELECT net_date, DATENAME(MONTH, CONVERT(VARCHAR, CAST(('01/' + SUBSTRING(@net_date, 1, 2) + '/' + SUBSTRING(net_date, 3, 4)) AS DATETIME), 103)) In the above code CONVERT function will convert the concatenated date format '01/06/2011' into '06/01/2011' and finally you will get the month name using DATENAME function. Hope it will help you. Good Luck :)

sushmita

@Ezineasp.net
Thanx a lot buddy... the query worked..
yipeee...

Phan Tom

SELECT CONVERT(VARCHAR(MAX), CAST('26-APR-2007' AS DATETIME), 103) 26/04/2007

ffer

goooddddddddddddddddddddddddddddddddddd work