Post reply

convert char to datetime

  • July 13, 2004 at 3:03 pm

    #161886

    How can I convert a char such as '10/10/2004' to a datetime type?

    thanks

  • July 13, 2004 at 3:25 pm

    #514467

    select convert(datetime,'10/10/2004')



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 13, 2004 at 11:35 pm

    #514502

    you could also try this:

    SELECT CAST(CONVERT(char(10), getdate(), 112) as datetime)

  • July 14, 2004 at 1:59 am

    #514512

    How about

    SELECT CONVERT(datetime,'10/10/2003',103)

     

    where 103 refers to the format of the date you recieve, can be substituted with a different number if you need a different format ... See BOL -> CAST & CONVERT

    greetings from Germany

    nano

     

  • July 14, 2004 at 1:31 pm

    #514539

    declare @mydt varchar(10)

    set @mydt = '10/10/2003'

    declare @newdt datetime

    set @newdt = Convert(datetime, @mydt)

    SELECT CONVERT(varchar(25),getdate(),2)

    SELECT CONVERT(varchar(30),@newdt,109)

    SELECT CONVERT(datetime,@mydt)

    SELECT CONVERT(datetime,'10/10/2003',103)

    SELECT CONVERT(datetime,@mydt,103)

    SELECT CAST(CONVERT(char(10), getdate(), 112) as datetime)

    Test the code above and you'll see that the last 3 select statements (which roughly correspond to the previous replies in this thread) all produce results that show the date in the same format, which is defined by your SQL Server date format settings. 

    If you want formatted output (and from the original question, I don't think that's the case) then you need to use a CONVERT to a char or varchar output, and it's only then for date formats that the style parameter is useful. 

    The last couple of select statements above are trying to do things that don't work for achieving formatted output.  So attempting to set a style (i.e., the 103 or 112, etc. values in the above) on the date is pointless. 

    If the point is to enter the char data into a datatime column in the database, you don't need to convert the value to a datetime (assuming it's a valid date), since the conversion is implicit.  Generally, in a situation like this, I'd validate in the application that it's a date (i.e., in a VB/VBScript you could use the isDate function to test the value and reject it if not in a date format) before attempting to store it in the DB. 

    So you could use one of the following CONVERT functions:

    SELECT CONVERT(datetime,@mydt)

    SELECT CONVERT(datetime,'10/10/2003')

    or use one of the following CASTs as follows:

    SELECT CAST(@mydt as datetime)

    SELECT CAST('10/10/2003' as datetime)

    I hope that helps you.

    ...gg

  • July 15, 2004 at 6:12 am

    #514627

    It very much depends on whereabouts in the world you are. '10/10/2004' is unambiguous, but '9/10/2004' could be the 9th of October in Britain or the 10th of September in the US. Check BOL for the style parameter you can use with CONVERT() - for British dates use 113, for US use 111. For others, read the Help.

    Scott Doughty (first time I've had something useful to contribute!)

    --
    Scott

  • July 15, 2004 at 4:51 pm

    #514772

    Alternatively...

    Don't convert '10/10/2004'!

    cast('20040101' as datetime) will work every time.

    The format is yyyymmdd and is unambiguous.

  • July 15, 2004 at 4:52 pm

    #514773

    make that cast('20041010' as datetime)

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

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