Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Convert Varchar to Datetime Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:57 AM
Points: 2,570, Visits: 4,655
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/
Post #1008390
Posted Thursday, October 21, 2010 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1008399
Posted Thursday, October 21, 2010 1:55 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 6:49 AM
Points: 861, Visits: 793
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
Post #1008789
Posted Thursday, October 21, 2010 11:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:57 AM
Points: 2,570, Visits: 4,655
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/
Post #1008957
Posted Saturday, October 23, 2010 6:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 6:49 AM
Points: 861, Visits: 793
Great idea!
Using a buffer for data validation before import.

Thanks,
Iulian
Post #1009603
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse