none
convert am/pm to 24 hour

    Question

  • i have this particular column which has am/pm and i would have to convert it to this particular format

    For example lets say i have 8:30 PM i would have to write the sql statement that converts the


    8:30 PM --> 2030
    2:30 PM --> 1430
    2:00 AM -->0200
    12 Noon --> 1200

    I am finding difficulty in writing the statement which converts the time into the particular format i want

    Would be eagerly waiting for your replies


    Thanks
    Monday, July 11, 2011 3:16 PM

Answers

  • Hi, try :

    select replace(convert(char(5),cast('8:30 PM' as datetime),108),':','') --2030
    select replace(convert(char(5),cast('2:30 PM' as datetime),108),':','') --1430
    select replace(convert(char(5),cast('2:00 AM' as datetime),108),':','') --0200

     

     

    Best regards
    • Marked as answer by Jack Nolan Monday, July 11, 2011 4:42 PM
    Monday, July 11, 2011 3:22 PM
  • Take a look at the CAST & CONVERT topic in BOL. 108 style converts datetime value to time format.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jack Nolan Monday, July 11, 2011 4:43 PM
    Monday, July 11, 2011 4:00 PM
    Moderator

All replies

  • Hi, try :

    select replace(convert(char(5),cast('8:30 PM' as datetime),108),':','') --2030
    select replace(convert(char(5),cast('2:30 PM' as datetime),108),':','') --1430
    select replace(convert(char(5),cast('2:00 AM' as datetime),108),':','') --0200

     

     

    Best regards
    • Marked as answer by Jack Nolan Monday, July 11, 2011 4:42 PM
    Monday, July 11, 2011 3:22 PM
  • Badii has the right idea. However, I would recomend you just cast it to a datetime. It's far more flexible in terms of presenting and updating the information then  leaving it as a char:

    select cast('8:30 PM' as datetime)
    


    Monday, July 11, 2011 3:27 PM
  • Thanks a lot for the reply. Before i mark the question as "Mark as Answer", I have another question :)

    How do we know what number to put in? The number that i am talking about is 108

    select replace(convert(char(5),cast('8:30 PM' as datetime),108),':','') --2030
    select replace(convert(char(5),cast('2:30 PM' as datetime),108),':','') --1430
    select replace(convert(char(5),cast('2:00 AM' as datetime),108),':','') --0200
    

    How do we determine which number to use? some use 8, 108, and so on

    Could you please tell me the reason behind those numbers ?


    Thanks
    Monday, July 11, 2011 3:54 PM
  • Take a look at the CAST & CONVERT topic in BOL. 108 style converts datetime value to time format.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jack Nolan Monday, July 11, 2011 4:43 PM
    Monday, July 11, 2011 4:00 PM
    Moderator
  • 108, convert to time format, as said Naomi, see this link please :

    http://www.sqlusa.com/bestpractices2005/centurydateformat/


    Best regards
    Monday, July 11, 2011 4:05 PM
  • Thanks..You solved my problem
    Monday, January 20, 2014 12:54 PM
  • You missed a fundamental concept. Temporal data is kept in an unknown, unexposed internal format. Since SQL is a tiered architecture, the display format is done in a presentation layer, never in the SQL query!  But the only display format in ANSI/ISO SQL is ISO-8601, which uses "yyyy-mm-dd HH-MM-SS"; so you are still writing 1960's COBOL, not real SQL! We have no AM/PM in real SQL code. 

    Until you use DATETIME2(0) and not COBOL strings, you will kludge your sub-standard dialect data with the 1970's Sybase/Unix CONVERT() string functions. Read about the "new" stuff on-line; we have DATE, TIME and DATETIME2(n) now. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, January 21, 2014 12:10 AM
  • From what I understand the ANSI SQL format and ISO-8601 are different - if you care going to call it ISO-8601 then you cannot omit the 'T' between the date and time portions.

    This can make a difference in SQL server depending on the datatype and language settings used.

    Tuesday, January 21, 2014 12:28 AM