Convert Varchar to Datetime

  • Hi,

    I need your help, I'm using this Query select convert(datetime,uploadtime,103) from tbl_00001 here uploadtime is varchar,this query work in my PC not in my client.Its very urgent.

    Kirubakaran K

  • Can you post some sample data from the table, so that we can check why it is not working.

    Which version of SQL does the client using

  • uploadtime format like this 30/09/2010 05:07:09 PM

    Actualy both are using SQL 2005

  • If this is very urgent then you should provide some more information to help people help you.

    Such as what exacltly do you mean by 'it does not work on the client PC'?

  • sharath.chalamgari (10/20/2010)


    Can you post some sample data from the table, so that we can check why it is not working.

    Which version of SQL does the client using

    uploadtime format like this 30/09/2010 05:07:09 PM

    Actualy both are using SQL 2005

  • What is the error message your client is getting?

    Probably your client has some data which is not in dd/mm/yyyy hh:mm:ss AM/PM format.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • exact error message will help us to resoleve or help u in this

  • steveb. (10/20/2010)


    If this is very urgent then you should provide some more information to help people help you.

    Such as what exacltly do you mean by 'it does not work on the client PC'?

    yes its live issue,

    select convert(datetime,uploadtime,103) from tbl_00001

    its give the data in my PC like

    2010-09-30 17:07:09.000

    2010-09-30 17:07:09.000

    2010-09-30 17:07:09.000

    2010-09-30 17:07:09.000

    2010-09-30 17:07:09.000

    2010-09-30 17:07:09.000

    2010-09-30 17:07:09.000

    in my client PC

    its throw error like

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

  • Hi sharath.chalamgari

    pls did you find any solution let me know.

  • kirubamca09 (10/20/2010)


    Hi sharath.chalamgari

    pls did you find any solution let me know.

    Your error message clearly says that there is some data in your clients side which is not in proper format

    Try the below mentioned query in your client's side and see if the results are in proper format

    SELECT * FROM tbl_00001 WHERE ISDATE(uploadtime) = 0

    This should give you a start

    You will have to do the dirty job of finding the records which are present in incorrect format in the clients side. Thats why many advise against using a VARCHAR datatype for dates


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (10/20/2010)


    kirubamca09 (10/20/2010)


    Hi sharath.chalamgari

    pls did you find any solution let me know.

    Your error message clearly says that there is some data in your clients side which is not in proper format

    Try the below mentioned query in your client's side and see if the results are in proper format

    SELECT * FROM tbl_00001 WHERE ISDATE(uploadtime) = 0

    This should give you a start

    You will have to do the dirty job of finding the records which are present in incorrect format in the clients side. Thats why many advise against using a VARCHAR datatype for dates

    Heare Problem is uploadtime column varchar...

  • there is data in the column that will not convert to datetime as it is not a valid date

    do as Kingston suggested and find out what the bad data is and then decide with your client what shoud be done with it..

  • Did you try the below query in the client's place

    SELECT * FROM tbl_00001 WHERE ISDATE(uploadtime) = 0

    If YES. What was the data you got from the query?


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (10/20/2010)


    Did you try the below query in the client's place

    SELECT * FROM tbl_00001 WHERE ISDATE(uploadtime) = 0

    If YES. What was the data you got from the query?

    its return all rows

    like

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

    30/09/2010 05:07:09 PM

  • OK. Now try the below queries and see if you get any data in incorrect format

    SELECT *

    FROM tbl_00001

    WHERE SUBSTRING( uploadtime, CHARINDEX( '/', uploadtime ) + 1, CHARINDEX( '/', uploadtime, CHARINDEX( '/', uploadtime ) + 1 ) - CHARINDEX( '/', uploadtime ) - 1 ) > 12

    This should give you data which is in mm/dd/yyyy hh:mm:ss AM/PM format

    Also try

    SELECT * FROM tbl_00001 WHERE ISDATE( uploadtime ) = 1


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 1 through 15 (of 24 total)

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