Using a Date field

You will want to use a datetime column.

>> You will want to use a datetime column.
I plan to, but what is the best way to insert the date (format) into it?

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

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.

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.

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 ...

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   ???

I did an Insert using "12/25/1960"
and it put this in the table:

12/25/1960 12:00:00 AM

>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

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 ?

just try it

declare @dt smalldatetime
Select @dt = '1900-01-01'
SELECT @dt

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 ...

thanks for the explanation(s)  . . .

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

What type of date calculation your are going to do?

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.

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

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

Yveau, yes that is what i meant to say, store the date in the format 20071021

thanks

Thanks everyone for your help. I really appreciate it.