Get date format as DD-MON-YYYY HH:MM AM

Question

Post reply

Get date format as DD-MON-YYYY HH:MM AM

ColdCoffee

SSC-Dedicated

Points: 39971
More actions
November 10, 2009 at 11:52 pm

#136122

Hi All,

Is there a direct way to get a date value (say GETDATE()) to this format "DD-MON-YYYY HH:MM AM"?

For Example:

Input Date Value = 2009-01-01 01:00:00.00 (assume it is AM)

Output required = ''01-Jan-2009 1:00 AM"

Input Date Value = 2009-01-01 01:00:00.00 (assume it is PM)

Output required = ''01-Jan-2009 1:00 PM"

I created this query :

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') +' '+STUFF(RIGHT( CONVERT(VARCHAR,GETDATE(),100 ) ,7), 6, 0, ' ')

This works pretty fine. But it involves lot of convert, cast, replace etc etc.. I want a simple way to get the output required. Is there any Styles in CONVERT function that I might use? or any in-built funtion in SSMS to do this?

I use SQL Server 2005.

Any help will be much appreciated!

Thanks in Advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Does this code help you?



declare @MyDate datetime;

set @MyDate = getdate();

select convert(varchar(30), @MyDate, 113);

select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM'

                                            else convert(varchar(30), @MyDate, 113) + ' AM' end;

set @MyDate = dateadd(hh, -3, getdate());

select convert(varchar(30), @MyDate, 113);

select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM'

                                            else convert(varchar(30), @MyDate, 113) + ' AM' end;

If you can use the 24 time, then you don't need the replace and concatentation code.

Paul White SSC Guru Points: 150457 More actions · Answer 2

Paul White

SSC Guru

Points: 150457

November 11, 2009 at 1:48 am

#1077680

SELECT dbo.FormatDateTime(CURRENT_TIMESTAMP, N'dd-MMM-yyyy hh:mm tt')

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 3

Of course, I cheated slightly - it depends on the CLR function below: 😀



using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Globalization;

public partial class UserDefinedFunctions

{

    [SqlFunction

        (

        DataAccess = DataAccessKind.None,

        SystemDataAccess = SystemDataAccessKind.None,

        IsDeterministic = true,

        IsPrecise = true

        )

    ]

    public static SqlString FormatDateTime

        (

        [SqlFacet(IsNullable = false)] SqlDateTime dateTime,

        [SqlFacet(IsNullable = false, MaxSize = 30)] SqlString format

        )

    {

        try

        {

            return (SqlString)dateTime.Value.ToString(format.Value, DateTimeFormatInfo.InvariantInfo);

        }

        catch (System.Exception)

        {

            return SqlString.Null;

        }

    }

};

It is very useful - any date/time format you like, without all that tedious messing about.

Of course, you could write something similar in T-SQL and wrap it in a function too - but it's not really the right tool for the job. The .NET routine can do it with just the one line really:

return (SqlString)dateTime.Value.ToString(format.Value, DateTimeFormatInfo.InvariantInfo);

I just jazzed it up a bit 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

Lynn Pettis (11/11/2009)

Does this code help you?

declare @MyDate datetime; set @MyDate = getdate(); select convert(varchar(30), @MyDate, 113); select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM' else convert(varchar(30), @MyDate, 113) + ' AM' end; set @MyDate = dateadd(hh, -3, getdate()); select convert(varchar(30), @MyDate, 113); select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM' else convert(varchar(30), @MyDate, 113) + ' AM' end;

If you can use the 24 time, then you don't need the replace and concatentation code.

Hi Lynn, thanks for the reply. But what I want is to remove the milliseconds from the date. Your code is giving out milliseconds also :ermm: I am actually pulling the dates from a column through a cursor, hence i need a compact code.

Paul White (11/11/2009)

SELECT dbo.FormatDateTime(CURRENT_TIMESTAMP, N'dd-MMM-yyyy hh:mm tt')

Thanks Paul,but i should not use CLR Procs nor i am allowed to create functions. I need simple T-SQL query to get it done

WHat about my query, do you people think it wont work?

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') +' '+STUFF(RIGHT( CONVERT(VARCHAR,GETDATE(),100 ) ,7), 6, 0, ' ')

i checked it and it is working fine!!

Paul White SSC Guru Points: 150457 More actions · Answer 5

COldCoffee (11/11/2009)

Thanks Paul,but i should not use CLR Procs nor i am allowed to create functions. I need simple T-SQL query to get it done

Thanks for mentioning that up front.

COldCoffee (11/11/2009)

WHat about my query, do you people think it wont work?

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') +' '+STUFF(RIGHT( CONVERT(VARCHAR,GETDATE(),100 ) ,7), 6, 0, ' ')

i checked it and it is working fine!!

Good for you.

It has a double space if the hour part of the time is a single digit, and the VARCHAR(11) should be a CHAR(11) but otherwise it seems fine.

I am resisting the urge to ask why you are doing this, why you think you need a cursor, why it needs to be 'compact' (number of characters?)...and so on.

I am assuming it is some kind of assignment or something.

edit: fixed a quote tag

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 6

It has a double space if the hour part of the time is a single digit, and the VARCHAR(11) should be a CHAR(11) but otherwise it seems fine.

Thanks for the comments Paul, am working on implementing them!;-)

I am resisting the urge to ask why you are doing this, why you think you need a cursor, why it needs to be 'compact' (number of characters?)...and so on.

I am assuming it is some kind of assignment or something.

I have a customer where i must send each users of their application, the date when their requests were processed. This will be a fairly smaller table, hence CURSOR dint hurt me. As for the DATE, that is the mandated format we must follow irrespective of the customer.

Why it should be compact is for the readability! Already i have created 5 SPs, hence i dint want to add one more funtion to get this DATE alone.

Cheers!