Convert Varchar to Datetime

Question

Convert Varchar to Datetime

Viewing 10 posts - 16 through 24 (of 24 total)

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

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 1

Kingston Dhasian (10/20/2010)
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

Both Queries are nothing return

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

That means that all your data is in the wrong format

like :

2010-09-30 17:07:09.000

you need to try something like

select Convert (datetime,substring(uploadtime,9,2) + '/' + substring(uploadtime,6,2) + '/' +substring(uploadtime,1,4),103)

kirubamca09 (10/20/2010)
Kingston Dhasian (10/20/2010)
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
Both Queries are nothing return

KirubakaranK SSC Enthusiast Points: 199 More actions · Answer 3

Hi All,

Thank you very much Kingston Dhasian for your replay.

Problem is data mismatch Some data dd/MM/yyyy format some other data's are MM/dd/yyyy format.

your query is very useful thanks a lot..

Kiruba

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Glad that i could help you. Now see if you can convert that column to DATETIME or a SMALLDATETIME datatype to avoid such problems in the future. It is not a great idea to store dates in VARCHAR datatype.

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/

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 5

What about storing data as int like this yyyymmdd? (year*10000 + month*100 + day)

The pros are:

the numerical order is the same as calendar order

and can be converted with CONVERT(datetime, x, 112) if you need to use dateadd and datediff functions for example

What are the pros and cons of this way of storing date as int yyyymmdd.

Thanks,

Iulian

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

Iulian -207023 (10/21/2010)
What about storing data as int like this yyyymmdd? (year*10000 + month*100 + day)
The pros are:
the numerical order is the same as calendar order
and can be converted with CONVERT(datetime, x, 112) if you need to use dateadd and datediff functions for example
What are the pros and cons of this way of storing date as int yyyymmdd.
Thanks,
Iulian

The pros and cons are similar to the ones you will be having when store it in a VARCHAR datatype

I am not sure about any pros, but there are some cons to this approach.

One thing being How will you stop the user from entering the date in any incorrect format?

You might want the user to store 03-February-2010 as 20100203 but the user may make a mistake and enter it as 20100302, which would become 02-March-2010 according to your assumption. These sort of issues will be almost impossible to rectify.

And all this trouble, when you have a datatype specially designed for storing dates. So its simply not advisable to store dates in any other datatype other than DATETIME, SMALLDATETIME or some new ones in SQL Server 2008.

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/

drew.allen SSC Guru Points: 76944 More actions · Answer 7

Iulian -207023 (10/21/2010)
What about storing data as int like this yyyymmdd? (year*10000 + month*100 + day)

There is a perfectly good datatype that is optimized for working with datetime data. It accommodates internationalization, it handles date arithmetic, and it handles leap years. Storing it as an int doesn't achieve any of these and converting back and forth costs cycles.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 8

Thank you Kingston and Drew,

you are right in SQL Server there are datetime data types with functions designed for this kind if data. yyyymmdd as int would not make sense, like date as varchar.

Things might change if I need to transfer the data from/to another system, non SQL Server, for example using CSV file, this yyyymmdd might be useful in this case, I am thinking that this yyyymmdd int would help me not to lose date wile transfering.

But for operational databases and for user inputs I absolutely agree with you.

Thanks again,

Iulian

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 9

Iulian -207023 (10/21/2010)
Things might change if I need to transfer the data from/to another system, non SQL Server, for example using CSV file, this yyyymmdd might be useful in this case, I am thinking that this yyyymmdd int would help me not to lose date wile transfering.

The best approach in this case would be to enter all the information from the CSV file into a temporary table( say Transactions_Temp ) where you can keep the datatype for dates as VARCHAR or INT and then validate the data for correctness of formats. Once you validate the data in temporary table and do the necessary error reporting, you can transfer the same to the main table( say Transactions ) where i would always suggest you to keep the datatypes for dates as DATETIME or SMALLDATETIME.

This method might add an overhead of additional table, but will be useful for error reporting and avoiding date conversion issues in the future.

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/

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 10

Great idea!

Using a buffer for data validation before import.

Thanks,

Iulian