Introduction
Sooner or later someone will ask you to include dates and/or times in a query or two. Sometime after you start using dates and/or times you will notice that data stored with the datetime data type doesn’t always react the way you expect in a query and that storing the date and time in non datetime data types creates other problems. In this article I plan to go over some of the things I have encountered when using dates and times.
Table Structure And Data For Examples
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDateTimes]')
and OBJECTPROPERTY(id,N'IsUserTable') = 1)
drop table [dbo].[tblDateTimes]
GO
CREATE TABLE [dbo].[tblDateTimes]
( [Row#] [int] NOT NULL ,
[cDateTime] [datetime] NULL ,
[cSmallDateTime] [smalldatetime] NULL ,
[cVarChar] [varchar] (30) NULL ,
[cChar] [char] (30) NULL
)
ON [PRIMARY]
GO
INSERT INTO tblDateTimes
([Row#], cDateTime, cSmallDateTime, cVarChar, cChar)
SELECT 1, '2004-08-27 13:57:10.498', '2004-08-27 13:57:10.498'
, '2004-08-27 13:57:10.498', '2004-08-27 13:57:10.498'
UNION
SELECT 2, getdate(), getdate(), getdate(), getdate()
UNION
SELECT 3, '3/1/1753 00:00:00.007', '3/1/1900 00:00:00.007'
, '3/1/1753 00:00:00.007', '3/1/1753 00:00:00.007'
UNION
SELECT 4, '2004-08-27 13:57:10.494', '2004-08-27 13:57:10.494'
, '2004-08-27 13:57:10.494', '2004-08-27 13:57:10.494'
UNION
SELECT 5, '2/1/1754', '2/1/1901', '2/1/1754', '2/1/1754'
Where Date/Times Can Be Stored
The datetime data type can store dates between 1 Jan 1753 and 31 Dec 9999. It can store times to the nearest 300th of a second such that you will always see the last digit stored as a 0, 3, or 7. One example would be 1:39:49.997. You will not be able to see the milliseconds in Enterprise Manager. Query Analyzer will display them.
The smalldatetime data type can hold dates between 1 Jan 1900 and 6 Jun 2079. It saves the time to the minute.
Dates and times can be stored in columns with other data types including the char and varchar data types.
To see how SQL Server stores data you can execute the sample code provided in the previous section. After you execute it run the following query:
SELECT * FROM tblDateTimes
You will get results similar to the below:
Row# cDateTime cSmallDateTime cVarChar cChar ----- ----------------------- ------------------- ----------------------- ----------------------- 1 2004-08-27 13:57:10.497 2004-08-27 13:57:00 2004-08-27 13:57:10.498 2004-08-27 13:57:10.498 2 2004-09-01 11:50:40.347 2004-09-01 11:51:00 Sep 1 2004 11:50AM Sep 1 2004 11:50AM 3 1753-03-01 00:00:00.007 1900-03-01 00:00:00 3/1/1753 00:00:00.007 3/1/1753 00:00:00.007 4 2004-08-27 13:57:10.493 2004-08-27 13:57:00 2004-08-27 13:57:10.494 2004-08-27 13:57:10.494 5 1754-02-01 00:00:00.000 1901-02-01 00:00:00 2/1/1754 2/1/1754
Now we can analyze how SQL Server handled each row inserted into tblDateTimes. For the 1st row cDateTime was rounded to the nearest 300th of a second, cSmallDateTime was rounded to the nearest minute, and the other two columns were stored as is. In the 2nd row we see that the date and time format is different for the columns cVarChar and cChar. For the 4th row we notice that the time for column cSmallDateTime is the same as the 1st row even though less milliseconds were specified. For the 5th row the dates for cDateTime and cSmallDateTime were reorganized so that the year is displayed first instead of the month and then a bunch of zeros were added to represent the time portion of those columns. cVarChar and cChar left the dates the way they were received.
Using Dates And Times
When you do comparisons using columns that contain dates and/or times you may get unexpected results. Make sure you understand how SQL Server handles dates and times to ensure you get the results you need.
Example 1
We want to see all rows that have a cDateTime greater than 27 Aug 04. When I began learning SQL I would have expected the below query to give me the results I wanted:
SELECT * FROM tblDateTimes WHERE cDateTime > '8/27/2004'
If you execute the above query you will see that the result set includes rows 1,2, and 4. I would have only expected row 2 to be in the result set. The reason the query didn’t do what I expected is that SQL Server adds time to the end of the date I specified and so includes all dates of 27 Aug 04 as long as the time with them is not midnight. I can modify the query as follows to exclude 27 Aug 04:
SELECT * FROM tblDateTimes WHERE cDateTime >= '8/28/2004'
Now only row 2 comes out which is what I wanted.
Example 2
I want to sort dates using the dates I stored in column cChar. Execute the following query in Query Analyzer:
SELECT * FROM tblDateTimes ORDER BY cChar
You will notice that the dates are not sorted from the oldest to the newest. SQL Server does not treat dates as dates when not stored in a column of data type datetime or smalldatetime. It sorted them by comparing the first character in each column and then compared the second character and so on. This is one of the down sides of using the char and varchar data types to store dates. In the Manipulating Dates And Times section I will show you how to fix the above query so that it will sort properly.
Manipulating Dates And Times
Example 3
To fix the dates so the order by in a previous example will work empty the table called tblDateTimes and then run the code below to repopulate the table.
INSERT INTO tblDateTimes
([Row#], cDateTime, cSmallDateTime, cVarChar, cChar)
SELECT 1, '2004-08-27 13:57:10.498', '2004-08-27 13:57:10.498'
, '2004-08-27 13:57:10.498', '2004-08-27 13:57:10.498'
UNION
SELECT 2, getdate(), getdate(), CONVERT(varchar(23),getdate(), 21)
, CONVERT(varchar(23),getdate(), 21)
UNION
SELECT 3, '3/1/1753 00:00:00.007', '3/1/1900 00:00:00.007',
CONVERT(varchar(23),CAST('3/1/1753 00:00:00.007' as datetime),21),
CONVERT(varchar(23),CAST('3/1/1753 00:00:00.007' as datetime), 21)
UNION
SELECT 4, '2004-08-27 13:57:10.494', '2004-08-27 13:57:10.494'
, '2004-08-27 13:57:10.494', '2004-08-27 13:57:10.494'
UNION
SELECT 5, '2/1/1754', '2/1/1901', CONVERT(varchar(23)
, CAST('2/1/1754' as datetime),21), CONVERT(varchar(23)
, CAST('2/1/1754' as datetime),21)
Now execute this query:
SELECT * FROM tblDateTimes ORDER BY cChar
Now the order by sorted the rows in the order desired. It worked because all the dates in cChar are in the same order having the year first and then the month and the day last.
Example 4
One problem I have run into when manipulating dates is assuming that when I am at the end of a month and add one month to it that I will still be at the end of the month. When you execute the following query you will see that this assumption is not always true.
DECLARE @Date datetimeSET @Date = '9/30/2004'
SELECT DATEADD(m,1,@Date) AS [1 Month Added], DATEADD(m,2,@Date) AS [2 Months Added]
That query gives the following result set:
1 Month Added 2 Months Added----------------------- ------------------------------------------------
2004-10-30 00:00:00.000 2004-11-30 00:00:00.000
You can see that I did not get October 31st. One way I have found to get the end of the month all the time is to go past the target month and set the day to the 1st and then subtract one day. This is a query that will do that:
DECLARE @Date datetimeSET @Date = '9/30/2004'
SELECT DATEADD(d,-1,DATEADD(m,2,STR(MONTH(@Date)) + '/1/' + STR(YEAR(@Date)))) AS [1 Month Added]
Now you get 31 October 2004. I used the STR function to avoid a syntax error. Without that function SQL Server tries to convert ‘/1/’ to an integer.
Conclusion
When working with dates be sure you test your queries enough to ensure they do what you expect. Make sure the dates and times are stored the way you want them to be stored. These are not all the ways you can manipulate dates, simply a few that I have found useful. Your comments are welcome.
