0

I have an nvarchar field which contains Date and Time data in the format 'Apr 2 2018 12:36PM' I need to extract the value as '1236'. While I have used combinations of substring and right I still need to remove the ':' and the 'AM or PM' and also substitute a leading space where the hour is less than 2 digits eg 0500. This TSQL would be integrated as the last piece of logic in an existing multiple case (shown below) which successfully uses TSQL case when logic to extract time from actual datetime fields. Any help would be much appreciated. Thankyou for your time

CASE
    WHEN Format(CAST(dbo.FieldSurveyData.TimeTestin AS datetime) , 'HHmm' , 'en-au') > 0
        THEN Format(CAST(dbo.FieldSurveyData.TimeTestin AS datetime) , 'HHmm' , 'en-au')
    WHEN Format(CAST(dbo.FieldSurveyData.TimeStartP AS datetime) , 'HHmm' , 'en-au') > 0
        THEN Format(CAST(dbo.FieldSurveyData.TimeStartP AS datetime) , 'HHmm' , 'en-au')
    WHEN Format(CAST(dbo.FieldSurveyData.TimeStar_1 AS datetime) , 'HHmm' , 'en-au') > 0
        THEN Format(CAST(dbo.FieldSurveyData.TimeStar_1 AS datetime) , 'HHmm' , 'en-au')
    WHEN Format(CAST(dbo.FieldSurveyData.TimeFini_2 AS datetime) , 'HHmm' , 'en-au') > 0
        THEN Format(CAST(dbo.FieldSurveyData.TimeFini_2 AS datetime) , 'HHmm' , 'en-au')
    ELSE ''
END
1

Assuming your values are all consistent, and you're looking for a one liner (because using a variable would be simpler) you should be able to use something of the following:

FORMAT(CONVERT(INT, REPLACE(REPLACE(REPLACE(LTRIM(RIGHT(YourField, 7)), 'AM', ''), 'PM', ''), ':', '')), '0000')

Here's the same code multi-line formatted:

FORMAT
(
    CONVERT
    (
        INT, 
        REPLACE
        (
            REPLACE
            (
                REPLACE
                (
                    LTRIM
                    (
                        RIGHT(YourField, 7)
                    ), 
                    'AM', 
                    ''
                 ), 
                 'PM', 
                  ''
             ), 
             ':', 
             ''
         )
     ), 
    '0000'
)

Take your pick. 🙂

By the way, SQL Server 2012 is end of life now (actually only on extended support for about another 7 months). If you were to upgrade to a newer version (2017 or later) you'd have access to the TRANSLATE() function which could be used as a single function call instead of the three REPLACE() function calls. This should theoretically be more performant but also less verbose of code, making it more readable and maintainable. Just some food for thought.

1
  • 1
    That was perfect @J.D. thankyou.. your solution was way out of my SQL logic league :)
    – Geospatial
    Nov 23 '21 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.