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

Question

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!

Why are you trying to format the date on the server instead of the client? Dates have no format. Just let the client application format it according to *its* rules. It's far easier to specify eg a culture for an entire report than try to format every single date on it, or set the time format for a field — Panagiotis Kanavos, Jun 02 '17 at 14:17
I could fix this on the webapp side probably. Was just hoping to get it stored in my repository in the format I was looking at. — John Waclawski, Jun 02 '17 at 14:49
There is nothing to fix. Dates have no format, they are binary values. If your repository classes expect a string for `backup_finish_date` you have a bug already. You should be using `DateTime` on your website as wll. You won't be able to sort those *strings* on your web page. — Panagiotis Kanavos, Jun 02 '17 at 14:53
In fact, if your web site's culture is `en-US` the time would appear in the way you want it with minimal changes. What you ask is the generic date format with short time, `g`. `DateTime.Now.ToString("g",CultureInfo.GetCultureInfo("en-US"))` will format the date in the way you want. All you have to do is set `g` to your controls' and grids' Format or DisplayFormat properties — Panagiotis Kanavos, Jun 02 '17 at 15:00
Panagiotis, you bring good points to the table and I think your route is the correct one for me. I am a DBA that started writing a webapp to make things easier for our developers. It started snowballing and got bigger than I was expecting. I'm learning as I am going. You've helped me two-fold and I very much appreciate it. I will go the route you suggested. Thank you very much! — John Waclawski, Jun 02 '17 at 15:50

score 2 · Answer 1 · edited Sep 22 '17 at 22:53

2

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

edited Sep 22 '17 at 22:53

Graham

7,431
18
59
84

answered Jun 02 '17 at 14:12

Siyual

16,415
8
44
58

Format is absolutely what I need. But as stated in my comment above, we still have several 2008 SQL Server' we're slowly migrating to 2016. As for the documentation link. Nothing in there is showing how to how to get it formatted to 3/31/2017 10:48:17 PM (for example). – John Waclawski Jun 02 '17 at 14:47

Kannan Kandasamy · Answer 2 · 2017-06-02T14:25:24.453

1

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

edited Jun 02 '17 at 14:25

answered Jun 02 '17 at 14:13

Kannan Kandasamy

13,405
3
25
38

Neither is CONVERT. Both format a date value into a string – Panagiotis Kanavos Jun 02 '17 at 14:17
Kannan, this appears to work for me on my newer versions of SQL Server but we still have a number of 2008 versions of SQL Server it won't work on. I am keeping this in my OneNote for future reference. Thanks! – John Waclawski Jun 02 '17 at 14:22

GarethD · Answer 3 · 2017-06-02T14:25:17.723

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.

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

3 Answers3