CODEWITHSUNDEEP
sql-servert-sqldatetimetype-conversionsql-convert
John Waclawski
John Waclawski

Reputation: 996

How to convert mm/dd/yyyy militarytime -> mm/dd/yyyy hh:mm:ss AM/PM

I'm using the following code and almost getting what I'm looking for:

 SELECT sdb.NAME AS DatabaseName
    ,COALESCE(CONVERT(VARCHAR(10), cast(max(bus.backup_finish_date) as date), 101) + ' ' + convert(varchar(12), max(bus.backup_finish_date), 108), 'Never Restored') as [LastBackupTime]
FROM sys.sysdatabases sdb
INNER JOIN dbo.backupset bus
    ON bus.database_name = sdb.NAME
GROUP BY sdb.NAME,
    bus.backup_finish_date

Your result should be something like: mm/dd/yyyy HH:mm:ss

I'm trying to get mm/dd/yyyy hh:mm:ss AM/PM

I've tried multiple converts, a series of casts, ltrim/right, and even offering homage to the T-SQL overlords. No luck yet.
I've even tried 

SELECT sdb.NAME AS DatabaseName
    --Code below needs changed to show Date & time--
    ,COALESCE(CONVERT(VARCHAR(30), MAX(bus.backup_finish_date), 100), 'Never 
backed up.') AS LastBackUpTime
FROM sys.sysdatabases sdb
INNER JOIN dbo.backupset bus
    ON bus.database_name = sdb.NAME
GROUP BY sdb.NAME,
    bus.backup_finish_date

but that gets me (for example) Mar 21 2017 10:47AM. We really prefer 3/21/2017 10:47AM.

Suggestions? I'm still picking this apart but could use some help.

Thanks!

Upvotes: 0

Views: 685

Answers (3)

Siyual
Siyual

Reputation: 16917

If you are using SQL Server 2012 or later, you can use FORMAT():

Select Format(Max(bus.backup_finish_date), N'MM/dd/yyyy hh:mm:ss tt')

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

One easiest way is to use format but it is not highly performant:

select FORMAT(Max(bus.backup_finish_date),'MM/dd/yyyy hh:mm:ss tt')

For earlier versions one another naive way of doing is as below:

Select CONVERT(VARCHAR(10), getdate(), 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), getdate(), 22), 11))

Instead of GetDate() use your date

But that is already mentioned by @GarethD So never mind

Upvotes: 1

GarethD
GarethD

Reputation: 69769

If you are using SQL Server 2012 or later you can use FORMAT, although be wary of doing this on large data sets.

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy hh:mm:sstt')

For earlier versions, or if performance is a concern, For earlier versions, or if performance is a concern, you can concatenate the date in the format MM/dd/yyyy (style 101), with the time in the format hh:mm:ss (style 8) and a case expression to determine AM or PM

SELECT  CONVERT(VARCHAR(10), GETDATE(), 101) + ' '
            + CONVERT(VARCHAR(10), GETDATE(), 8)
            + CASE WHEN DATEPART(HOUR, GETDATE()) < 12 THEN 'AM' ELSE 'PM' END

HOWEVER, formatting is a job for the presentation layer. If it was me doing this, then I would just send the native datetime, including nulls back to the presentation layer and let this handle it. It means that in your application layer you can still work with the dates, perform date calculations, or sort etc without the worry that 15/01/2017 is going to appear after 02/02/2017. It also means you can display dates in the end user's preferred locale, rather than yours.

Upvotes: 1

Related Questions