Post reply

Convert varchar

  • December 4, 2012 at 4:55 am

    #271196

    is there any way to convert varchar value to TIME datatype?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 5:06 am

    #1565234

    CONVERT(TIME,varcharcolumn)

  • December 4, 2012 at 6:10 am

    #1565256

    when i try to do this it gives me error-

    declare @date varchar(20)

    select @date=CONVERT(time,convert(int,'5',1))

    print @date

    Msg 529, Level 16, State 2, Line 3

    Explicit conversion from data type int to time is not allowed.

    how to resolve his?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 6:13 am

    #1565258

    Int to Time is not allowed

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

  • December 4, 2012 at 6:18 am

    #1565261

    Is there any other way to do this?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 6:24 am

    #1565263

    Just convert direct from a VARCHAR value, but need to ensure it is of the correct formatting for it to convert, '5' will not convert to time.

  • December 4, 2012 at 6:31 am

    #1565272

    plz give me an example..

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 6:32 am

    #1565273

    CONVERT(TIME,'01:05',1)

  • December 4, 2012 at 6:34 am

    #1565276

    i want an example of converting int to time...

    i cant change the datatype of column to varchar

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 6:36 am

    #1565278

    Well how do you plan to format the INT into the required format as a VARCHAR to convert to TIME?

    That is why INT cannot convert to TIME.

  • December 4, 2012 at 7:40 am

    #1565326

    kapil_kk (12/4/2012)

    i want an example of converting int to time...

    i cant change the datatype of column to varchar

    You weren't asking how to convert an INT to TIME, this was your original question:

    kapil_kk (12/4/2012)

    is there any way to convert varchar value to TIME datatype?

    If you want to convert an INT to TIME, you need to tell us more about this value. What does it represent? Is it the number of seconds since midnight, or is it an integer representation of the time, what? You simply haven't provided us with the information needed to give you an answer.

  • December 4, 2012 at 10:50 pm

    #1565567

    See you can use the substring function to break the int value into time

    or into the format you want have I think this will do the trick .

  • December 5, 2012 at 9:25 am

    #1565818

    Its ugly but this will convert int to time, no check if valid time is made except if more than 6 digits, note you could go into milliseconds etc by just extending same logic

    declare @iTest as integer, @cTime as char(10)

    select @iTest = 120433

    select @cTime = case when len(convert(char(10),@iTest))< 1 then '00:00:00'

    else case when len(convert(char(10),@iTest))< 2 then '00:00:0' + convert(char(10),@iTest)

    else case when len(convert(char(10),@iTest))< 3 then '00:00:' + convert(char(10),@iTest)

    else case when len(convert(char(10),@iTest))< 4 then '00:0' + substring(convert(char(10),@iTest),1,1) + ':' + substring(convert(char(10),@iTest),2,2)

    else case when len(convert(char(10),@iTest))< 5 then '00:' + substring(convert(char(10),@iTest),1,2) + ':' + substring(convert(char(10),@iTest),3,2)

    else case when len(convert(char(10),@iTest))< 6 then '0' + substring(convert(char(10),@iTest),1,1) + ':' + substring(convert(char(10),@iTest),2,2) + ':' + substring(convert(char(10),@iTest),4,2)

    else case when len(convert(char(10),@iTest))< 7 then substring(convert(char(10),@iTest),1,2) + ':' + substring(convert(char(10),@iTest),3,2) + ':' + substring(convert(char(10),@iTest),5,2)

    else 'bad time' end end end end end end end

    select convert(time,@cTime)

    results:

    ----------------

    12:04:33.0000000

    (1 row(s) affected)

    Rick

Viewing 13 posts - 1 through 12 (of 12 total)

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