Post reply

Time Convert

  • April 5, 2011 at 7:24 pm

    #248228

    Here is my question I have source data like this

    Column_Name(Time)

    1425

    945

    1258

    And target field is Time

    How I can change Military time to normal time

    Time

    2:25 AM

    9:45 PM

    12:58 AM

    Thanks in advance.

  • April 5, 2011 at 9:05 pm

    #1307782

    How's this?

    DECLARE @test-2 TABLE (MyTime VARCHAR(4));

    INSERT INTO @test-2

    VALUES ('1425'),

    ('945'),

    ('1258');

    WITH cte AS

    (

    SELECT MyTime,

    MyNewTime = CONVERT(TIME, STUFF(RIGHT('0000'+MyTime,4),3,0,':'))

    FROM @test-2

    )

    SELECT MyTime,

    MyNewTime = CASE WHEN RIGHT(CONVERT(VARCHAR(5), MyNewTime, 109),1) = ':'

    THEN CONVERT(VARCHAR(4), MyNewTime, 109)

    ELSE CONVERT(VARCHAR(5), MyNewTime, 109)

    END + ' ' +

    RIGHT(CONVERT(VARCHAR(25), MyNewTime, 109),2)

    FROM cte;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 5, 2011 at 9:07 pm

    #1307784

    tooba111 (4/5/2011)

    Here is my question I have source data like this

    Column_Name(Time)

    1425

    945

    1258

    And target field is Time

    How I can change Military time to normal time

    Time

    2:25 AM

    9:45 PM

    12:58 AM

    Thanks in advance.

    It would help if you provided DDL to make it easier to test. In the examples you show I can see how you can determine the AM/PM as 945 in military time certainly isn't 9:45 PM.

    Are you saying the target column is named Time, or is of the Time data type? Is the type of the source column time like you are indicating, or is it a CHAR, VARCHAR, or number type?

  • April 5, 2011 at 9:16 pm

    #1307785

    If what Wayne produced is what you want, I might simplify his code just a little:

    DECLARE @test-2 TABLE (MyTime VARCHAR(4));

    INSERT INTO @test-2

    VALUES ('1425'),

    ('945'),

    ('1258');

    WITH cte AS

    (

    SELECT MyTime,

    MyNewTime = CONVERT(VARCHAR(8), CONVERT(TIME, STUFF(RIGHT('0000' + MyTime, 4), 3, 0, ':'), 100), 100)

    FROM @test-2

    )

    SELECT MyTime,

    MyNewTime = STUFF(MyNewTime, LEN(MyNewTime) - 1, 0, ' ')

    FROM cte;

    This version should perform better if you have a lot of rows as it converts the time to a string fewer times.

    Isn't STUFF a wonderful thing?

  • April 5, 2011 at 9:21 pm

    #1307786

    Thank you all of you guys.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply