One of the reader Nanda of SQLAuthority.com has posted very detailed script of converting any date time in desired format. I suggest every reader of this blog to save this script in your permanent code bookmark and use it when you need it.
Refer the function and get familiar yourself with different format this function support. I have added few examples of how this function can be used at the end of the article. You can download whole code in ZIP format as well.
Download Get Date Time in Any Format Script (ZIP)
CREATE FUNCTION [dbo].[ufsFormat]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80)
DECLARE @TwelveHourClock INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT
-- (c) Pinal Dave http://www.SQLAuthority.com
SELECT @ReturnedDate='error! unrecognised format '+@format
SELECT @DateFormat=CASE @format
WHEN 'mmm dd yyyy hh:mm AM/PM' THEN 100
WHEN 'mm/dd/yy' THEN 1
WHEN 'mm/dd/yyyy' THEN 101
WHEN 'yy.mm.dd' THEN 2
WHEN 'dd/mm/yy' THEN 3
WHEN 'dd.mm.yy' THEN 4
WHEN 'dd-mm-yy' THEN 5
WHEN 'dd Mmm yy' THEN 6
WHEN 'Mmm dd, yy' THEN 7
WHEN 'hh:mm:ss' THEN 8
WHEN 'yyyy.mm.dd' THEN 102
WHEN 'dd/mm/yyyy' THEN 103
WHEN 'dd.mm.yyyy' THEN 104
WHEN 'dd-mm-yyyy' THEN 105
WHEN 'dd Mmm yyyy' THEN 106
WHEN 'Mmm dd, yyyy' THEN 107
WHEN 'Mmm dd yyyy hh:mm:ss:ms AM/PM' THEN 9
WHEN 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' THEN 9
WHEN 'Mmm dd yy hh:mm:ss:ms AM/PM' THEN 109
WHEN 'mm-dd-yy' THEN 10
WHEN 'mm-dd-yyyy' THEN 110
WHEN 'yy/mm/dd' THEN 11
WHEN 'yyyy/mm/dd' THEN 111
WHEN 'yymmdd' THEN 12
WHEN 'yyyymmdd' THEN 112
WHEN 'dd Mmm yyyy hh:mm:ss:Ms' THEN 113
WHEN 'hh:mm:ss:Ms' THEN 14
WHEN 'yyyy-mm-dd hh:mm:ss' THEN 120
WHEN 'yyyy-mm-dd hh:mm:ss.Ms' THEN 121
WHEN 'yyyy-mm-ddThh:mm:ss.Ms' THEN 126
WHEN 'dd Mmm yyyy hh:mm:ss:ms AM/PM' THEN 130
WHEN 'dd/mm/yy hh:mm:ss:ms AM/PM' THEN 131
WHEN 'RFC822' THEN 2
WHEN 'dd Mmm yyyy hh:mm' THEN 4
ELSE 1 END
SELECT @ReturnedDate='error! unrecognised format ' +@format+CONVERT(VARCHAR(10),@DateFormat)
IF @DateFormat>=0
SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
--check for favourite and custom formats that can be done quickly
ELSE IF @DateFormat=-2--then it is RFC822 format
SELECT @ReturnedDate=LEFT(DATENAME(dw, @Date),3) + ', ' + STUFF(CONVERT(NVARCHAR,@Date,113),21,4,' GMT')
ELSE IF @DateFormat=-4--then it is european day format with minutes
SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113)
ELSE
BEGIN
SELECT @Before=LEN(@format)
SELECT @Format=REPLACE(REPLACE(REPLACE( @Format,'AM/PM','#'),'AM','#'),'PM','#')
SELECT @TwelveHourClock=CASE WHEN @Before >LEN(@format) THEN 109 ELSE 113 END, @ReturnedDate=''
WHILE (1=1)--forever
BEGIN
SELECT @pos=PATINDEX('%[yqmidwhs:#]%',@format+' ')
IF @pos=0--no more date format strings
BEGIN
SELECT @ReturnedDate=@ReturnedDate+@format
BREAK
END
IF @pos>1--some stuff to pass through first
BEGIN
SELECT @escape=CHARINDEX ('\',@Format+'\') --is it a literal character that is escaped?
IF @escape<@pos BEGIN
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1) +SUBSTRING(@format,@escape+1,1)
SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80))
CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1)
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
SELECT @pos=PATINDEX('%[^yqmidwhs:#]%',@format+' ')--get the end
SELECT @ReturnedDate=@ReturnedDate+--'('+substring(@Format,1,@pos-1)+')'+
CASE SUBSTRING(@Format,1,@pos-1)
--Mmmths as 1--12
WHEN 'M' THEN CONVERT(VARCHAR(2),DATEPART(MONTH,@Date))
--Mmmths as 01--12
WHEN 'Mm' THEN CONVERT(CHAR(2),@Date,101)
--Mmmths as Jan--Dec
WHEN 'Mmm' THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date))
--Mmmths as January--December
WHEN 'Mmmm' THEN DATENAME(MONTH,@Date)
--Mmmths as the first letter of the Mmmth
WHEN 'Mmmmm' THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date))
--Days as 1--31
WHEN 'D' THEN CONVERT(VARCHAR(2),DATEPART(DAY,@Date))
--Days as 01--31
WHEN 'Dd' THEN CONVERT(CHAR(2),@date,103)
--Days as Sun--Sat
WHEN 'Ddd' THEN CONVERT(CHAR(3),DATENAME(weekday,@Date))
--Days as Sunday--Saturday
WHEN 'Dddd' THEN DATENAME(weekday,@Date)
--Years as 00--99
WHEN 'Yy' THEN CONVERT(CHAR(2),@Date,12)
--Years as 1900--9999
WHEN 'Yyyy' THEN DATENAME(YEAR,@Date)
WHEN 'hh:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
WHEN 'hh:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
--tthe SQL Server BOL syntax, for compatibility
WHEN 'hh:mi:ss:mmm' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'H:m:s' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,8),':0',':'),2,30)
WHEN 'H:m:s:ms' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,12),':0',':'),2,30)
--Hours as 00--23
WHEN 'hh' THEN REPLACE(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2),' ','0')
--Hours as 0--23
WHEN 'h' THEN LTRIM(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2))
--Minutes as 00--59
WHEN 'Mi' THEN DATENAME(minute,@date)
WHEN 'mm' THEN DATENAME(minute,@date)
WHEN 'm' THEN CONVERT(VARCHAR(2),DATEPART(minute,@date))
--Seconds as 0--59
WHEN 'ss' THEN DATENAME(second,@date)
--Seconds as 0--59
WHEN 'S' THEN CONVERT(VARCHAR(2),DATEPART(second,@date))
--AM/PM
WHEN 'ms' THEN DATENAME(millisecond,@date)
WHEN 'mmm' THEN DATENAME(millisecond,@date)
WHEN 'dy' THEN DATENAME(dy,@date)
WHEN 'qq' THEN DATENAME(qq,@date)
WHEN 'ww' THEN DATENAME(ww,@date)
WHEN '#' THEN REVERSE(SUBSTRING(REVERSE(CONVERT(CHAR(26), @date,109)),1,2))
ELSE
SUBSTRING(@Format,1,@pos-1)
END
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
END
RETURN @ReturnedDate
END
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', '#')
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com) , Nanda
A very good article for asp.net newbies…Thanks Dave!!
hey,,this needs to be checked out
ELSE 1 END
should be
ELSE -1 END
in line 52
Hi Pinal,
The Above function is not working when i give like the below..
SELECT [dbo].[ufsFormat] (’8/7/2008′, ‘dd/mm/yy hh:mm:ss:ms AM/PM’)
Hey Boss ur a Genious
[...] If you want your date in any other format refer the UDF SQL SERVER – Get Date Time in Any Format – UDF – User Defined Functions. [...]
Hi Pinal ,
i want to extract time part of date .. but this function is not working …
SELECT [dbo].[ufsFormat] (’2008-09-02 14:14:00.000′, ‘hh:mm:ss:ms AM/PM’)
giving result as :
09/02/08
Hi,
When i am passing @date input as dd/mm/yyyy’ and wanted to convert into ‘mm/dd/yyyy’. I am getting following error
“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”.
Could you please let me know what would be the best approach for it when i want to pass date as ‘dd/mm/yyyy’ and wanted the output as ‘mm/dd/yyyy’.
Thanks
Robin
Hi Robin,
It looks like your data might have invalid date.
You can use CONVERT(VARCHAR(100), GETDATE(),101) to display output in mm/dd/yyyy.
Thanks,
Tejas
thanks Pinal for your SQL script. :)
Hi Pinal,
I have a situation in one of my project requirement.
I want date format like ” , “.
Is it possible in sql 2005?
I hope I will find it out asap.Please help me out.
Regards,
Siva
Pinal,
Happy birthday.
I have a table that has an integer field that records time taken as a whole number(don’t ask). For example 1650 = 16:30, or 35 = 00:21. Once the data is summed I want to be able to convert to a hh:mm format.
I have drafted this SQL but get error “Syntax error converting the varchar value ’0:0′ to a column of data type int.”
DECLARE @SQL_HOLDER nvarchar(3000)
SET @SQL_HOLDER = ‘
declare @hoursall int
declare @hours decimal(18,2)
UPDATE visits
set @hoursall = travel_time,
@hours = @hoursall/100,
travel_time = convert(varchar, convert(int, @hours)) + ”:” + convert(varchar, convert(int, 60 * (@hours – convert(int, @hours)))) ‘
EXEC sp_executesql @SQL_HOLDER
GO
Michael
@MJDA
It might help to see the actual SQL statement that causes the error.
Also, the conversion can be slightly easier than you posted. For minutes, i’d do it slightly differently:
DECLARE @A INT
SET @A = 1650
SELECT CAST(@A / 100 AS VARCHAR(2)) + ‘:’ + CAST (CAST(60 * ((@A % 100) / 100.0) AS INT) AS VARCHAR(2))
i want to store date in sql server 2000 in ‘dd/mm/yyyy’ formate can it is posible please tell me and give me the query how to write it.
@Mayur,
I have written two scripts, Execute them and check the results.
Script 1:
create table #Example1 ( Id int , Date Datetime )
insert into #Example1 values ( 1 , getdate())
select Id, convert (varchar, date, 101) from #Example1
Script 2:
Create table #Example2 ( Id int, Date varchar(11))
insert into #Example2 values ( 1 , convert (varchar(11),getdate(), 101))
select * from #Example2
drop table #Example1 , #Example2
~IM.
@Mayur.
Above format is mm/dd/yy
I forgot you asked, this format : dd/mm/yyyy, to get this format, just replace, 101 by 103 in the above script.
Script 1:
create table #Example1 ( Id int , Date Datetime )
insert into #Example1 values ( 1 , getdate())
select Id, convert (varchar, date, 103) from #Example1
Script 2:
Create table #Example2 ( Id int, Date varchar(11))
insert into #Example2 values ( 1 , convert (varchar(11),getdate(), 103))
select * from #Example2
drop table #Example1 , #Example2
Hi Pinal,
I want following .Net DatTime Format dd/M/yyyy h:mm:ss tt in SQL Server 2005:
I have date in SQL Server is: 2009-11-10 16:20:42.017
and I want result as: 10/11/2009 4:20:42 PM
Regards,
Ankit
Do the formation at .NET
What is the difficulty you are having there?
Hi, Madhivanan
Sorry for delay reply.
Actually I found solution, reference link given by Tejas Shah. thnx Tejas and you also.
heY bOSS U R GENIOUS
Hi Ankit,
Please find attached link for the same:
h ttp://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/
Thanks,
Tejas
hi dave ,
diz babu .i am new to diz blog,i have doubt that,
how to generate the dates in between given dates in sql,
ex :i have date’s 1/11/09 & 2/12/09.if i want to display the dates in between these two .how can i move.
waiting for the reply.plz kindly give me an idea as early as possible.
declare @from datetime ,@to datetime
select @from=’2009-11-01′,@to=’2009-12-02′
select
dateadd(day,number,@from) as dates
from master..spt_values
where type=’p’ and number between 0 and datediff(day,@from,@to)
this is the respnse of the parent form.
Hello pinal dev sir
can we convert current date like following formate
if date is 08/06/2010 then i want to convert like
8th June 2010
if my date is 21/06/2010 then i want
21st June 2010
please tell me
Thanks
Hi my clients need to display date time in following format.
dd/mm/yy hh:mm:ss
Like
29/11/20 11:49:30
Not AM or PM
Day/Months/year(only last two digit) time in 24 Hour format.
How can I do this.
I am using SQL Server 2008 ans asp.net 3.5.
Please help me.
Thanks
Hi All ,
Can any one answer how to get a series of dates with in a particular starting and ending date ?
For example my starting is ’01/18/2011′ and my ending date is ’01/25/2011′, i need to get all the dates between those dates like,
Output:
01/18/2011
01/19/2011
01/20/2011
01/21/2011
01/22/2011
01/23/2011
01/24/2011
select dateadd(day,number,cast(’20110118′ as datetime)) from master..spt_values
where dateadd(day,number,cast(’20110118′ as datetime))<'20110125' and type='p'
Thanks for your reply Madhivanan,
The following code also will return the same result,
Here i have used CTE,
please check the code.
declare @varstartdate datetime
declare @varenddate datetime
declare @vardiff int
set @varstartdate = ’01/18/2011′
set @varenddate = ’01/25/2011′
set @vardiff = DATEDIFF(d,@varstartdate,@varenddate)
declare @s varchar(max)
set @s=’with numbers as
(
select 1 as Num, convert(datetime,”’ + convert(varchar,@varstartdate,101) + ”’) as vardate1
union all
select Num + 1, convert(datetime,”’ + convert(varchar,@varstartdate,101) + ”’) + Num from numbers where Num < ' + convert(varchar,@vardiff) + '
)
select convert(varchar,vardate1,101) from numbers option(Maxrecursion 25)'
execute (@s)
CONVERT(CHAR(24), @dtGetDate, 103), converts format of @dtGetDate to 103.
Can there be a way to know in which format @dtGetDate is?
What is the datatype of @dtGetDate?
need a format DD/MM/YYYY HH:MI:SS how could i perform it
how to display in DD – MMM – yyyy where dash are compulsory . Not DD MMM YYYY
Hi,
I have date format like this
select [dbo].[ufsFormat]
(’09-Jan-2012 3:15:58 PM’, ‘dd-Mmm-YYYY hh:MM:SS AM/PM’) and i am getting the result 09-Jan-2012 3:15:58 PM.
But i need output is like(have to add zero in my hours when it is in 12 hours format) 09-Jan-2012 03:15:58 PM.
I am using Sqlserver2005.
Can any one help me out.
hi
i need a style format value for MM/dd/yyyy hh:mm:ss AM
For eg like this 02/05/2012 12:20:23 AM
Nithin
hi
i need a style format value for MM/dd/yyyy hh:mm:ss AM
For eg like this 02/05/2012 12:20:23 AM
I am using Sql server 2008 R2
Nithin
Have a look at CONVERT function in SQL Server help file
I have one column in a varchar format which represent year and month in ‘YYYYMM’ fromat. How can I extract the year and month from it i.e. if the data is ’201104′, I want it in the form of ’2011 APR’?
Can anyone help me out?
SELECT CONVERT(varchar, CONVERT(datetime, ’2014/12/30′), 100)
Hi,
is it possible to convert Jan 25,2012 to 2012-01-25? pls tell possible solution. thanks
How can i get 12 Hour Time in following format
5:20 PM
I have two columns “InTime” & “OutTime” both having type DateTime
I want to show the InTime and OutTime in following format
Hour:Minute AM/PM
also that i want the difference of OutTime and InTime In Hours and Minutes separately.
Help me in such regards
Thanks
[...] Get Date Time in Any Format – UDF – User Defined Functions [...]
How can I change ‘Fri Jan 11 00:00:00 EST 2013′ into datetime format
Does it work on sql server 2008 R2?