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. Let us learn about User Defined Functions.

Refer the function and get familiar yourself with a different format this function support. I have added a few examples of how this function can be used at the end of the article. You can download the whole code in ZIP format as well.

Download Get Date Time in Any Format Script (ZIP)

I am not including the entire function over here as it can be very long it is very difficult to format. I strongly encourage that you download the function from the URL which I have listed above. I personally find it very helpful and interesting.

SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy') 'Format mm/dd/yy'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss') 'Format hh:mm:ss'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm') 'Format mmm'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM') 
	'Format Mmm dd yyyy hh:mm:ss:ms AM/PM'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', '#') 'Format #'
GO

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

Let me know what you think of this function. I strongly encourage that you try this out. However, in the latest version of SQL Server, there is a better function called FORMAT which can the date and time in a better format.

Let me know what you think of this new blog article SQL SERVER – Learning New Multipurpose FORMAT Function.

Reference: Pinal Dave (https://blog.sqlauthority.com/), Nanda

SQL DateTime, SQL Function, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQLAuthority News – Authors Personal Website Renovate – SQL Centric Website
Next Post
SQL SERVER – 2008 – Introduction to Online Indexing Operation

Related Posts

51 Comments. Leave new

  • 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

    Reply
    • select dateadd(day,number,cast(‘20110118’ as datetime)) from master..spt_values
      where dateadd(day,number,cast(‘20110118’ as datetime))<'20110125' and type='p'

      Reply
      • 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?

    Reply
  • need a format DD/MM/YYYY HH:MI:SS how could i perform it

    Reply
  • how to display in DD – MMM – yyyy where dash are compulsory . Not DD MMM YYYY

    Reply
  • Shaik Nazeer Hussain
    January 9, 2012 12:20 pm

    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.

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

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

    Reply
  • 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?

    Reply
  • Hi,
    is it possible to convert Jan 25,2012 to 2012-01-25? pls tell possible solution. thanks

    Reply
  • How can i get 12 Hour Time in following format
    5:20 PM

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

    Reply
  • How can I change ‘Fri Jan 11 00:00:00 EST 2013’ into datetime format

    Reply
  • Does it work on sql server 2008 R2?

    Reply
  • I am passing @week =First week @Day=Monday,@month=’MAY’,@Year=2014 in result = date like 05/05/2014

    Reply
  • Thank You Dave!!! Sergio Aranda

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

    Reply
  • 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
    please mail me : [email removed]

    Thanks in Advance
    srinivas

    Reply
  • How to print the date as char like 3 1 1 0 2 0 1 4…

    Kindly help me.

    Reply
  • Deal Pinal,

    I have written once report procedure to print the data in bank cheque like Name, Amount and Amount In Words.. In that I need to print the date (31/10/2014) into char like 3 1 1 0 2 0 1 4….

    Kindly help me out ASAP….

    Reply
  • Hi,

    Can i get the date displayed in dd*mm*yyyy format? If yes, How??

    Reply

Leave a Reply