Day Month Year D.B. fields vs YMD field using date functions

Question

Day Month Year D.B. fields vs YMD field using date functions

Mark F-428640

Hall of Fame

Points: 3659
More actions
September 28, 2007 at 11:45 am

#118361

Please provide some general guidlines on whether it is better to represent in a D.B. a fields for Year, Month, Day or is it better to have a field YMD and use date functions in your queries to use the particular part of the date necessary for a where clause.
i.e.
select someFields
from someTable
where Day = 25
and Month = 12
and Year = 2000
or
select someFields
from someTable
where Day(YMD) = 25
and Month(YMD) = 12
and Year(YMD) = 2000
I am looking for the most efficient approach.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Personally, store a date as a date (datetime). If you store '8/29/1959' in a datetime field , it looks like 1959-08-29 00:00:00.000, and you can query the date in a where clause as: WHERE MyDate = '1959-08-29'.

😎

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 2

Hi Lynn,

Sorry I may not have been very clear I was in a rush when I posted this. I am more concerned with the efficiency of using the data. I just want to know if it is better to store the data in one field as datetime as you suggested and use date functions to work with the necessary parts of the date i.e. day -- month -- year

or

is it better to store the data with datetime with the full date and three more fields with each date part day -- month -- year and query them individually.

keep in mind speed of execution is my goal here.

I guess all I am saying is it faster to use a function to generate the date part or to read the date part from a table?

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

It's hard to say. The advantage of datetime fields is that they understand calendars. They know months, years, etc. and can move through those structures easily. Addition and subtraction are the really important things here.

If you're doing straight queries, the tinyint/smallint values will query very quickly. However the datetime is stored internally as an offset, not a character or other structure, so I'm not sure how much faster ints would be.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Let's put it this way :

2007 = smallint (2 bytes)

12 = tinyint (1 byte)

31 = tinyint (1 byte)

So you have to process 4 bytes here in 3 columns (small overhead I assume here for 3 columns VS 1)

OR

'2007/12/31' AS SMALLDATETIME = 4 bytes as well. But now you get to access all the cool datetime functions of sql server and have more indexing options. I don't see any downside of using smalldate in this case. It even has the option to keep the time (with 1 minute precision)... which you would have to keep in another column in the first scenario (2 bytes more if you want to complicate your life, 6 bytes if kept as strings... but forget any easy math operations from that point on).

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Simply put, there are more reasons than I could possibly post here as to why you should not, nay, must not store dates as 3 separate columns. The efficiency for your current use may look attractive, but it will fail in the future and so will other "efficiencies".

A clever person might try the best of both worlds... store the dates as DATETIME... modifiy the table by adding 3 calculated columns for Y, M, and D. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

And, God Forbid, those 3 calculated columns could even be indexed. There's no reason to index all three but you could ;).

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Mark Fyffe (9/28/2007)
Please provide some general guidlines on whether it is better to represent in a D.B. a fields for Year, Month, Day or is it better to have a field YMD and use date functions in your queries to use the particular part of the date necessary for a where clause.
i.e.
select someFields
from someTable
where Day = 25
and Month = 12
and Year = 2000
or
select someFields
from someTable
where Day(YMD) = 25
and Month(YMD) = 12
and Year(YMD) = 2000
I am looking for the most efficient approach.

Most efficient?

Here it is:

select someFields

from someTable

where YMD = '2001225'

_____________
Code for TallyGenerator