Post reply

Convert 0000 into 00:00 format

  • March 3, 2010 at 2:39 pm

    #211806

    Hi Friends

    here is the situation i am facing.I have dispatch time column like this.

    1200

    1345

    1245

    So i want to convert into lik ethis

    12:00

    13:45

    12:45

    Any ideas please?

    Thanks.

  • March 3, 2010 at 2:54 pm

    #1128116

    One way would be something like :

    declare @Time int

    select @Time = 0630

    select

    SUBSTRING(CAST(@Time + 10000 AS varchar),2,2) + ':' + SUBSTRING(CAST(@Time + 10000 AS varchar),4,2)

    Converting oxygen into carbon dioxide, since 1955.

  • March 3, 2010 at 2:56 pm

    #1128119

    It depends on several things:

    What is the format the original values are stored as?

    How do you want to display times before 10:00 (w/ or w/o leading zero)?

    Why is'n it stored as TIME format already?

    And the main question: Why do you want to do it within SQL Server instead of doing the formatting with the app/frontend?

    DECLARE @i INT

    SET @i = 900

    SELECT RIGHT(1000+@i/100 ,2) +':' +RIGHT(@i,2)

    DECLARE @C VARCHAR(4)

    SET @C = '900'

    SELECT LEFT(@c1,LEN(@c1)-2)+':' +RIGHT(@c1,2)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 3, 2010 at 3:07 pm

    #1128124

    Lutz,

    Very creative. Good work!

  • March 3, 2010 at 4:07 pm

    #1128151

    If your time is stored as character data, try this:

    declare @i varchar(4)

    set @i = '1'

    select stuff(right('0000'+@i,4),3,0,':')

    if it's stored as numeric data, try this:

    declare @i int

    set @i = 1

    select stuff(right('0000'+convert(varchar(4),@i),4),3,0,':')

    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

  • March 3, 2010 at 4:10 pm

    #1128154

    Thanks friends,

    It worked excellent..............

    Once again thanks for your help...

  • March 3, 2010 at 5:16 pm

    #1128179

    anitha.cherukuri (3/3/2010)

    Thanks friends,

    It worked excellent..............

    Once again thanks for your help...

    There are 3 different examples above... which one did you end up using?

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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