Results 1 to 3 of 3

Thread: Time conversion

  1. #1
    Join Date
    Oct 2003
    Location
    LaPorte, Indiana
    Posts
    1

    Exclamation Unanswered: Time conversion

    I am trying to convert time currently in this format: HH24:MI to this format: HH24.MI. However there are some caveats.

    Time is formatted HH24:MI. The MI will either be :30 or :00. If the time is on the half hour (:30) the time should read 20.5 for 20:30. If the time is 20:00 the time should read 20. I am still a beginner at SQL and looked through all of the online documentation I can but am still coming up short. Anyone got any suggestions?

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    hope this helps

    gives you a general idea on how to go about it using a case statement. You might have to change data types.... Leave that to you.

    select 'Time' = case when datepart(mm,getdate()) >= 30
    then cast(datepart(hh,getdate())as varchar(2)) +'.5'
    else cast(datepart(hh,getdate())as varchar(2)) end

  3. #3
    Join Date
    Oct 2003
    Location
    USA
    Posts
    4

    Re: Time conversion

    Using select statement , we can display time or DateTime in different formats i.e using to_char
    select to_char(sysdate,'HH:MIS') FROM DUAL;
    or
    select to_char(sysdate,'HH.:MM.S.') from dual;

    if you have any validations as you mentioned like , regarding half hour etc, you need to extract Hour or Minute Or second by using functions and we need to display in required formats.



    Originally posted by richcta
    I am trying to convert time currently in this format: HH24:MI to this format: HH24.MI. However there are some caveats.

    Time is formatted HH24:MI. The MI will either be :30 or :00. If the time is on the half hour (:30) the time should read 20.5 for 20:30. If the time is 20:00 the time should read 20. I am still a beginner at SQL and looked through all of the online documentation I can but am still coming up short. Anyone got any suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •