Convert Varchar to Datetime

Question

Post reply

Convert Varchar to Datetime

KirubakaranK

SSC Enthusiast

Points: 199
More actions
October 20, 2010 at 5:24 am

#228117

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

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

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

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

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

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 2

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

Actualy both are using SQL 2005

steveb. SSC-Forever Points: 46733 More actions · Answer 3

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'?

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 4

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

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/

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 6

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

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 7

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

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

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 8

Hi sharath.chalamgari

pls did you find any solution let me know.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 9

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/

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 10

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...

steveb. SSC-Forever Points: 46733 More actions · Answer 11

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..

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 12

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/

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 13

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 14

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/