Post reply

Convert Varchar to Date??

  • July 30, 2013 at 9:00 am

    #296197

    Hi

    I have a date field stored in a varchar as "Jul 24 2013 8:05AM"

    I would like to convert to a date field to do calculations like this "2013-07-24 00:00:00.000"

    Time is not important..

    Thanks in Advance

    Joe

  • July 30, 2013 at 9:08 am

    #1637030

    What's wrong with a simple CAST or CONVERT?

    SELECT CONVERT( datetime, 'Jul 24 2013 8:05AM')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 30, 2013 at 9:09 am

    #1637031

    CAST will accomplish what you are looking for.

    select cast(cast('Jul 24 2013 8:05AM' as date) as datetime)

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • July 30, 2013 at 9:46 am

    #1637052

    Thanks so much....

    I bit of a brain crap ... or just being a SPAZZ!!

    I was testing with

    DECLARE @Date varchar(8)

    set @Date='Jul 24 2013 8:05AM'

    SELECT CONVERT( datetime, @date)

    select cast(cast('Jul 24 2013 8:05AM' as date) as datetime)

    wondering why I was coming up with 2002-07-24......

    BTW While I'm asking stupid questions....

    How do I color code my code in replies?

    Thanks Again and have a great date

  • July 30, 2013 at 10:11 am

    #1637062

    There are some IFCode tags available to the left of your screen when you're replying. For SQL you need to use [ code="sql"][ /code] (without space).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 31, 2013 at 1:21 am

    #1637244

    I prefer this method (sql 2008 + )

    SELECT CONVERT(DATE,'Jul 24 2013 8:05AM')

    Gives the result:

    2013-07-24

    [font="Times New Roman"]There's no kill switch on awesome![/font]

  • July 31, 2013 at 8:26 am

    #1637402

    Your varchar is to small for the data you are putting into it, so it is only taking the first 8 characters and then trying to convert that to a date. Increase the varchar size to at least 11.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

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

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