Advantages of specific time formats... | Tech Off

jonathansampson
stick a scissor in you eye!

Working on a database and was just thinking what format I wanted to store my dates in. I don't need h:m:s, so I'm wondering if I should use the traditional YYYY-MM-DD, or a Unix Timestamp - do you guys know of any disadvantages or advantages that come with either one of those? Or perhaps I should store h:m:s, even though I don't immediately have a use for them - perhaps it's smarter to just keep them around just in case...

Jonathan Sampson

littleguru
allein, allein,... allein, allein!

Well... from the space standpoint you will always use the same. Therefore you might go with the full thing (date + time) in case you want to use the time later on. Internally the database will save the datetime in some UNIX kind-of-style.

foreachdev
Doesn't he look like a winner

In reply to littleguru

#Jul 1st @ 2:47 PM

SQL Server 2008 has Date Type.

http://devlicio.us/blogs/sergio_pereira/archive/2008/04/06/the-new-data-types-in-sql-server-2008.aspx

Also you could make a UDT (user defined type) and always force the time to 00:00:00. Which is probably essentially what MSFT did to implement it Smiley

littleguru
allein, allein,... allein, allein!

In reply to foreachdev

#Jul 1st @ 2:58 PM

And in the end it gets saved as a number (UNIX style), only that the time is set to zero in the Date type (they choose the number that's representing the date with the time as zero)... It's always a long (or any other big number; I don't know exactly if they use a long) that they use to save the date; may it be date/time or date.

figuerres
???

in your data-access code just do something like:

DateTime ToStoreDate = somedate.Date or
DateTime ToSToreDate = DateTime.Today

that will give you the date and a time of 00:00:00 / midnight for that day.

then if you do any date work later your dates will be "just dates" and all the data will be clean and std.

BUT! I tend to use a DateTime in my data like this:

table xxxx
ID Not Null INT Identity(1,1)
CreationTimeStamp DateTime Not Null default GetDate()
...

so that when a new record is added i save the moment in time when it was created.
none of my code ever allows that to be modifed and I use that in Update statements along with the ID and other data to make sure which record is used.

but if its say the date of a loan, date of an event, date of inventory etc... then use the .Today or .Date methods of a datetime to clean out the hh:mm:ss stuff.

Sven Groot
You can't have everything; after all, where would you put it?

If your storage format is meant to be human readable, use yyyy-mm-dd (e.g. if it's XML; actually, for XML use the xsd:date format, which is close to that). Otherwise I'd use a timestamp.

jonathansampson
stick a scissor in you eye!

Thanks for the input guys.

Pace
In The Mix...

In reply to jonathansampson

#Jul 2nd @ 7:46 AM

From what I know the datatype datetime stores exactly that, DateTime. Also, it stores it the way it wants to store it, its down to the way you select it which dictates the format of it

You could always use the SET DATEFORMAT DMY (or MDY) etc at your selection time to show it in the way you want or you can always convert to a char and chop bits out. Being in the uk I use a lot of CONVERT(CHAR(10), GetDate(),103) AS Date which for today would ouput 02/07/2008

And if I wanted to query based on a uk date I would always start with
SET DATEFORMAT DMY
SELECT
    SomeField,
    CONVERT(CHAR(10), DateNeeded,103) As Date
FROM
    MyTable
WHERE
    Date > '01/01/08'

So to recap, most important bit is its just a data type. It doesnt really care about formatting until you come to consume it in someway.

hth
Pace