?
Solved

Using a Date field

Posted on 2007-10-18
23
Medium Priority
?
215 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:MikeMCSD
  • 8
  • 5
  • 3
  • +3
23 Comments
 
LVL 1

Expert Comment

by:ExistenceExists
ID: 20103288
You will want to use a datetime column.
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20103320
>> 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
 
LVL 1

Assisted Solution

by:ExistenceExists
ExistenceExists earned 200 total points
ID: 20103444
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 15

Expert Comment

by:dosth
ID: 20103482
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 800 total points
ID: 20103572
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20103649
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
 
LVL 15

Assisted Solution

by:dosth
dosth earned 200 total points
ID: 20103760
cant you have a bigint as data type and store the date like this

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

thanks
Dosth
0
 
LVL 1

Expert Comment

by:ExistenceExists
ID: 20103859
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20104532
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20104838
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
 
LVL 16

Author Comment

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

12/25/1960 12:00:00 AM
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20105202

>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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20105410
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20105829
just try it

declare @dt smalldatetime
Select @dt = '1900-01-01'
SELECT @dt
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20107237
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
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20111070
thanks for the explanation(s)  . . .

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


0
 
LVL 15

Expert Comment

by:dosth
ID: 20111164
What type of date calculation your are going to do?
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20111292
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
 
LVL 15

Expert Comment

by:dosth
ID: 20111399
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
 
LVL 35

Expert Comment

by:James0628
ID: 20112806
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
 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 800 total points
ID: 20114402
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
 
LVL 15

Expert Comment

by:dosth
ID: 20117695
Yveau, yes that is what i meant to say, store the date in the format 20071021

thanks
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 20117943
Thanks everyone for your help. I really appreciate it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question