get custom date time format

Question

I want to display date time as eg. Dec 1, 09 11:22:45 PM using SQL query

Currently my format is :

 DATENAME(Month, (((MachineGroups.TimeAdded*10000000)+ 621355968000000000) -599266080000000000) / 864000000000) + SPACE(1) + DATENAME(d, (((MachineGroups.TimeAdded*10000000)+ 621355968000000000) - 599266080000000000) / 864000000000) +', ' + DATENAME(year, (((MachineGroups.TimeAdded*10000000)+621355968000000000) - 599266080000000000) /864000000000) + SPACE(1)+DATENAME (hour,(((MachineGroups.TimeAdded*10000000)+621355968000000000) - 599266080000000000) / 864000000000) + ':' +DATENAME (minute,(((MachineGroups.TimeAdded*10000000)+ 621355968000000000) - 599266080000000000) / 864000000000) + ':' +DATENAME (second,(((MachineGroups.TimeAdded*10000000)+ 621355968000000000) - 599266080000000000) / 864000000000) AS Expr2

Ussing the above i get eg. December 1, 2009 23:22:45

I tries using the cuatom formata of "MMM" and "yy" but it did not work

any suggestions??? thanks

Is there no way i can use the Datename property above to get my desired format??

Answer 1

It is much easier and more efficient to return the value as a generic datetime object and format it in your UI.

What is your motivation for returning a formatted date from the database?

Answer 2

This will get you everything but AM/PM:

declare @myDate datetime set @myDate = getdate() select LEFT(DATENAME(MM, @myDate),3) + ' ' + RIGHT('0'+DATENAME(DD, @myDate),2) + ', ' + RIGHT(DATENAME(YY, @myDate),2) + ' ' + convert(varchar,(DATEPART(hour, @myDate))) + ':' + convert(varchar,(DATEPART(minute, @myDate))) + ':' + convert(varchar,(DATEPART(second, @myDate)))

There are various ways to achieve the AM/PM values, among them would be a substring of:

SELECT convert(varchar, getdate(), 109)

Answer 3

As well as MONTHNAME (which you're already using), check out DATEPART. It's also not far from the CONVERT format of 9 [via CONVERT (VARCHAR(20), @datetime, 9)], so you could manipulate this as well.

Yet another option is using two CONVERTs ... this gives you what you want, but the time's in 24hr format:

SELECT CONVERT(VARCHAR(20), @datetime, 107) + ' ' + CONVERT (VARCHAR(20), @datetime, 108)

Use a different conversion function and a bit of string manipulation to get you 12 hour with AM/PM. Or do something like this:

SELECT CAST(DATEPART(hh, @datetime) - 12 AS VARCHAR)
     + ':' + CAST(DATEPART(mi, @datetime) AS VARCHAR) 
     + ':' + CAST(DATEPART(ss, @datetime) AS VARCHAR) 
     + CASE WHEN DATEPART(hh, @datetime) BETWEEN 0 AND 11 THEN ' AM' ELSE ' PM' END

Neither this or manipulating the output of CONVERT is pretty, but they're your best options.

HOWEVER: as others have pointed out though, this is normally better done client/UI-side rather than SQL-side.

Answer 4

You can find all supported SQL Server formats here.

To select in a format equivalent to Dec 1, 09 11:22:45 PM, you could use the date from format 7: Mon dd, yy. The time can be assembled from format 109: mon dd yyyy hh:mi:ss:mmmAM (or PM). Combined:

select 
    convert(varchar(10), getdate(), 7) + 
    ' ' +
    stuff(
        substring(
            convert(varchar(32), getdate(), 109)
        ,13,14) -- Substring HH:mi:ss.mmmAM
    ,9,4,' ') -- Replace .mmm by one space

This should print:

Nov 24, 09  4:58:36 PM

The second of the two spaces between 09 and 4 is reserved for a two-number hour, like 11:59:59 PM. :)