SQL SERVER – Get Date Time in Any Format – UDF – User Defined Functions

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

About these ads

49 thoughts on “SQL SERVER – Get Date Time in Any Format – UDF – User Defined Functions

  1. 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’)

  2. Pingback: SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2 Journey to SQL Authority with Pinal Dave

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

  4. 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

  5. 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

  6. 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

  7. @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))

  8. 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.

  9. @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.

  10. @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

  11. 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

  12. 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)

  13. 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

  14. 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

  15. 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

      • 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)

  16. CONVERT(CHAR(24), @dtGetDate, 103), converts format of @dtGetDate to 103.
    Can there be a way to know in which format @dtGetDate is?

  17. 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.

  18. 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

  19. 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?

  20. 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

  21. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

  23. Hi sir,i have one doubt , is there any patch file for sqlserver ,means i want sqlserver but its size is min 4GB ,but i want use only tables and view and procedures only these are my requirement ,so i dont want install 4GB file ,i am looking for small patch or small size sqlserver file ,it would be work for tables ,views ,procedures …please give me that file if any

    Thanks in Advance
    srinivas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s