0

I've seen many questions related to the format and date/time conversion in SQL Server but none answer my concern (it is not duplicated). For example.

When executing this statement:

select 
    convert(varchar(20), cast('2018/03/05 18:00:00.000' as time), 100) as entryTime

I get this result:

enter image description here

I want the output to be 06:00:00 p.m.

Is there any way to get a result like this: 06:00:00 p.m.?

Environment: SQL Server 2008 R2

Unfortunately, the FORMAT and FORMATDATETIME functions are not yet available in this SQL Server version.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
J. Rodríguez
  • 256
  • 1
  • 6
  • 21
  • what's the real benefit in formatting through sql? best to do this over the application. in SQL a date is a date a time is a time, comparing of these variables will need casting. – RoMEoMusTDiE Mar 05 '18 at 22:08
  • I think this is something you should address in the presentation layer. It's technically possible to get something like this in sql (e.g. using style 22 instead of 100, using a ltrim + right('0' + ..., 12) trick to insert a 0 at the beginning, etc) but you should really just keep the date/time formats as is in SQL. – ZLK Mar 05 '18 at 22:10
  • I need to get just that format for something I'm doing, that format as is with the lowercase letters – J. Rodríguez Mar 05 '18 at 22:13

3 Answers3

2

Using right, string concatenation and replace I managed to avoid using the value more than once for the appropriate result:

SELECT  REPLACE(
            REPLACE(
                RIGHT(
                    '0000000000' + 
                        CONVERT(
                        varchar(10), 
                        cast('2018/03/05 18:00:00.000' as time(0)), 
                        109),
                10), 
            'PM', 
            ' p.m.'),
        'AM',
        ' a.m.') as entryTime

Result:

entryTime
06:00:00 p.m.
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

I haven't tested this, as I'm on my phone, but perhaps. Now tested and corrected:

STUFF(RIGHT(CONVERT(varchar(26),GETDATE(),109),14),9,4,' ')
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • It does not work, with this you get as a result: ` 6:20:21 7P` – J. Rodríguez Mar 05 '18 at 22:19
  • 1
    Try: SELECT STUFF(RIGHT('0' + LTRIM(RIGHT(CONVERT(varchar(40),CONVERT(DATETIME, '2018/03/05 18:00:00.000'),109),14)),14),9,4,' ') – MJH Mar 05 '18 at 22:31
  • As I said, I'm on the phone, so I couldn't test (sorry), however, you should also be able to make simple changes like fixing a slight numbering error. ;) Also, oddly, edit is missing below my post, so I can't fix it... – Thom A Mar 05 '18 at 23:00
  • I was telling the OP to try that, I took your code and debugged it but it wouldn't have felt right posting a new answer and taking all the credit so I edited your answer so you would get the credit, but now it's waiting for peer review so I put it in the comments as well. Then I upvoted your answer for doing this on your phone! :) – MJH Mar 05 '18 at 23:05
  • 1
    I've fixed it now. @MJH, no need for the `LTRIM`,or double `CONVERT`, it simply needed to be changed from a `varchar(25)` to `varchar(26)` and to `9,4` from `10,3`. – Thom A Mar 05 '18 at 23:07
  • The LTRIM was for the leading zero on times between 0 and 9. – MJH Mar 05 '18 at 23:11
1
SELECT SUBSTRING(CONVERT(VARCHAR(26), CAST('2018/03/05 18:00:00.000' as  DATETIME), 109), 12, 8) 
       + ' ' + 
       LOWER(SUBSTRING(CONVERT(VARCHAR(26), CAST('2018/03/05 18:00:00.000'  as DATETIME), 109), 25, 1))
       + '.' + 
       LOWER(SUBSTRING(CONVERT(VARCHAR(26), CAST('2018/03/05 18:00:00.000' as DATETIME), 109), 26, 1))
       + '.'

Updated more complete solution as for the leading zero need more info if you are using variable to test if it's greater than 10

seesharpguru
  • 151
  • 8