Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: How to convert datetime from varchar to datetime

    hi,
    How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.

    following are the sample data that is theer in the older table for the date.


    12/12/2003
    1/13/2007
    01132004
    1-1-2004
    1.2.2001



    there is no uniformity of the data that is stored currently.



    thnkx in adv.
    rahul jha

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by DBA_Rahul
    How to convert datetime from varchar to datetime?
    The answer to that is
    Code:
    Convert(datetime, <value>)
    However you cannot trust this conversion to be accurate for each format of literal string.
    I suggest you test the lot and see how you get on.

    Rememer that SQL Server uses American datetime MMDDYYYY. For your example of 12/12/2003 it will happily accept 12/31/2003 and will fail on 31/12/2003.

    This is a horrible, horrible problem that should NEVER of occured.
    I advise you read this and this thoroughly!
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    ur rgt george. this is a horrible problem. but i have to take it. thr is no other choice.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please note that
    Code:
    Convert(datetime, '01132004')
    Will convert but throw up an error.
    This format cannot be trusted!

    Test each format of your code thoroughly!
    Code:
    SELECT myDatetimeValue,
    CASE myDatetimeValue
      WHEN LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN
        Convert(datetime,myDatetimeValue)
      WHEN LIKE '[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] THEN
        Convert(datetime,myDatetimeValue)
      WHEN LIKE ...
    END As [converted]
    FROM myTable
    Last edited by gvee; 09-11-07 at 07:44.
    George
    Home | Blog

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    thnkx for the suggestion. din thought of on this line.



    thnkx
    rahul jha

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Rememer that SQL Server uses American datetime MMDDYYYY.
    nope

    SQL Server uses whatever format you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    By default it's MMDDYYYY..?
    I didn't just make that up - I read it somewhere... now if only I could find out where!
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    also, "WHEN LIKE" won't work either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CASE WHEN myDatetimeValue LIKE ...
    I should have mentioned it was purely pseudo code

    and linky!
    Quote Originally Posted by Jeff Smith
    QA returns dates as YYYY-MM-DD -- which is neither British or American, it is just a universal date format. I don't think you can easily change this in QA. It is definitely a short-coming in that application, it would be nice to have more control over how different data types are formatted and does lead to lots of confusion over presentation-versus-data when people feel forced to do it in T-SQL to see things the way they want in QA.

    In SQL Server Management Studio it will use your computer's regional date time settings.
    Quote Originally Posted by georgev
    For your example of 12/12/2003 it will happily accept 12/31/2003 and will fail on 31/12/2003.
    This is true for <= SS2K as far as I am aware.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rahul, since you are using scripts, may i suggest that you convert the varchar dates one format at a time

    for example, if the varchar date contains periods or dashes, replace them with slashes, and then you will have xx/xx/xxxx or x/x/xxxx, and you can convert these using CONVERT(DATETIME,datefld,101)

    if the varchar date contains no periods or dashes or slashes, like 01132004, you can first re-arrange it to 20040113 and then convert it using style 112 like this --

    CONVERT(DATETIME,SUBSTRING(datefld,5,4)+SUBSTRING( datefld,1,4),112)

    obvioulsy you have to know which format you're dealing with, so that's why you'd handle them separately
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    georgey, your jeffy linky does not prove that "SQL Server uses American datetime MMDDYYYY"

    yes, i know i am hard on you -- but i do it because i care, and because you are such a good learner


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're not being hard on me, are you?
    If I don't get pulled up about stuff like this then I'll carry on ignorant to the fact that I'm doing it wrong!

    No it does not prove it, but it does highlight that QA actually uses the universal (logically, come to think of it) 20071231 (YYYYMMDD) format.
    I was basing my assumption on the following:
    Code:
    SELECT Convert(datetime,'31/12/2007') As [1]
    SELECT Convert(datetime,'12/31/2007') As [2]
    Quote Originally Posted by Rudy
    i do it because i care, and because you are such a good learner
    That might have to enter the old sig
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •