Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert Varchar to Datetime


Convert Varchar to Datetime

Author
Message
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4994
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
drew.allen
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2599 Visits: 9878
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Iulian -207023
Iulian -207023
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1226
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
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3001 Visits: 4994
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
Iulian -207023
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1226
Great idea!
Using a buffer for data validation before import.

Thanks,
Iulian
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search