Using a Date field

I'll need to be performing a lot of calculations using a Date field, so
I would like to know the best way to store it for later use.

How should I store the date?  Like:  09/03/2007  ???       thanks
LVL 16
MikeMCSDAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
I agree with the above comments, if you need to perform lots of date operations you should go for either 'Datetime' or 'smalldatetime'; as SQL Server 2005 or its predicessors does not have any particular datatype to store date only you should use either of these two. If you just wanna store date alone, then i would prefer smalldatetime as this requires less storage space compared to 'datetime'
>How should I store the date?  Like:  09/03/2007  

Datetime values are internally stored as integers, so you dont have to worry about this, the only issue with your representation is, sql wont know whether you are looking for Sept 3 or March 09. so better use the YYYY-mm-DD format
0
 
ExistenceExistsCommented:
You will want to use a datetime column.
0
 
MikeMCSDAuthor Commented:
>> You will want to use a datetime column.
I plan to, but what is the best way to insert the date (format) into it?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ExistenceExistsCommented:
There is not better or worse format to insert into the datetime colum.  It is only a matter of whether it is able to parse that format in the proper way.  You can insert a date as mm/dd/yyyy or mm-dd-yy and it will accept both.  SQL server stores the date in it's own internal format and then defaults the display unless you specifically format the display yourself.

Hope this helps.
0
 
dosthCommented:
By default, SQL Server will store a datetime in the form of MM/DD/YYYY
HH:MM:SS am/pm, i.e., "5/3/2002 1:17:39 AM"

when you retrive you can do the conversion what ever you need

Thanks
Dosth
0
 
MikeMCSDAuthor Commented:
thanks everyone
I'll need to store dates with years below 1990, like 1950, so
I won't be able to use smalldatetime, otherwise I would.
0
 
dosthCommented:
cant you have a bigint as data type and store the date like this

20071018 = convert(varchar(8),getdate(),112)

thanks
Dosth
0
 
ExistenceExistsCommented:
dosth,

you could, but then you lose all the functionality of a datetime field and all of its supporting functions.  If he is planning on doing a lot of calculations, then it sounds like he is going to need those.
0
 
YveauCommented:
Just a small advise ... I found that the safest way to enter a date for SQL Server is in the YYYYMMDD format, that way it will *** NEVER *** make a misinterpretation on months or days when entering 01032007 there is always the discussion on whether March 1st or January  3rd was intended. Even when using separators it's not 100% clear.

Hope this helps ...
0
 
MikeMCSDAuthor Commented:
Thanks Yveau,
I'm still a little confused, . .
When GetDate() inserts the date it looks like
this in the table:    8/22/2005 1:27:00 PM

So when I Insert the date into the datetime field
I should use  2007/01/03  ???

and not  01/03/2007   ???
0
 
MikeMCSDAuthor Commented:
I did an Insert using "12/25/1960"
and it put this in the table:

12/25/1960 12:00:00 AM
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

>I'll need to store dates with years below 1990, like 1950, so I won't be able to use smalldatetime, otherwise I >would.

smalldatetime can store dates  from January 1, 1900, through June 6, 2079
0
 
MikeMCSDAuthor Commented:
The book I'm looking at says the minimum value
for smalldatetime is Jan. 1, 1990  ???
That's for SQL 2000.  Is 1900 for 2005 ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
just try it

declare @dt smalldatetime
Select @dt = '1900-01-01'
SELECT @dt
0
 
YveauCommented:
From BOL (SQL 2005):
datetime (two 4 byte integers, 8 bytes stored)
 January 1, 1753, through December 31, 9999
 3.33 milliseconds
 
smalldatetime (two 2 byte integers, 4 bytes stored)
 January 1, 1900, through June 6, 2079
 1 minute
 
So actually, you don't 'store' the date 8/22/2005 1:27:00 as such, but as something like this: 38584|5220000.
The way this date is presented to you is only a format. In the any datetime is stored as a time difference between the entered date and the startdate of the data type (see above)

Hope this clears things out ...

0
 
MikeMCSDAuthor Commented:
thanks for the explanation(s)  . . .

Still not sure which format I should use,
should it be
        2007/01/03
or
        20070103         ??    


0
 
dosthCommented:
What type of date calculation your are going to do?
0
 
MikeMCSDAuthor Commented:
That date is going to be a Birthday.
I'm going to need to check for when the Birthday occurs
four weeks before the birthday, and then on the Birthday.
0
 
dosthCommented:
i am sure this will help, store the data in the format as specified
20071018 = convert(varchar(8),getdate(),112)

and when you check convert the date again to this format

say FiledName = convert(varchar(8),getdate(),112)

for four weeks before

FiledName = convert(varchar(8),getdate()-28,112)

you can use int as data type, almost int / date both use 4 bytes

Thanks
Dosth
0
 
James0628Commented:
From the SQL 2000 Help:

 datetime

 Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

 smalldatetime

 Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.


 I suspect that the 1990 in the book you mentioned is a typo.  IAC, as was already suggested, you can just try putting a date before 1990 in a smalldatetime variable and see if it works.

 As for which format you should use, datetime and smalldatetime are always stored the same way.  The only "format" question is how you structure a date string when you put it into one of those fields, compare it with one of those fields, etc.  SQL has to interpret the string correctly.  02/03/07 can be interpreted differently, depending on your system.  On our system, it's Feb 03, 2007, but it might be different on your system.  One option is to put a 4-digit year first.  As was already mentioned, if SQL sees a 4-digit year first, it interprets the date as YYYYMMDD.  That gives you a consistent format.  Or, you can just determine how your system interprets a date like 02/03/2007 and use that format.  The interpretation should be consistent, unless something is changed on your system.

 As for using "/" or some other separator, I don't know if they're always necessary, but I always use them.

 Hope this helps.

 James
0
 
YveauCommented:
As I mentioned above, when you enter a date in the format 'YYYYMMDD' SQL Server will *** NEVER *** be confused on what date you want to enter. No separator, just 20071020 for today.
-->> insert into Table (Birthday) values ('20071020')

When you want to retrieve the date from the database, you can deside what format to use by the convert() function:
-->> select convert(char(10),Birthday,101) from Table
will result in
-->> 10/20/2007

Here is a description of all convert formats (I used 101 in the example, which is the default for the US):
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Hope this helps ...
0
 
dosthCommented:
Yveau, yes that is what i meant to say, store the date in the format 20071021

thanks
0
 
MikeMCSDAuthor Commented:
Thanks everyone for your help. I really appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.