Working with SQL Server Date/Time Variables
Displaying Dates and Times in Different Formats
Searching for Particular Date Values and Ranges
This is the first article in a series of articles that I will discuss various aspects of working with SQL Server date/time columns. SQL Server has two different date/time columns.
They are DATETIME and SMALLDATETIME. This article will define the difference between these two SQL Server date/time data types, as well as show you how to insert date and time data into SQL Server DATETIME and SMALLDATETIME columns.
The DATETIME column is used to hold a date and time value, where time is accurate to three-hundredth of a second. The date for a DATETIME column can range from January 1, 1753 to December 31, 9999. A DATETIME column takes 8 bytes of disk storage. The physical storage of these 8 bytes is divided into 2 - 4 byte integer pieces. The first 4 byte integer is used to store the number of days before or after the base date January 1, 1900, while the second 4 bytes integer is used to represent the number of milliseconds since midnight.
A SMALLDATETIME column also holds a date and time value, but the time portion is only accurate to one minute. Valid dates for a SMALLDATETIME column can range from January 1, 1900 to June 6, 2079. The SMALLDATETIME column takes 4 bytes of storage. This 4 bytes is broken into two 2 byte integer pieces. The first 2 bytes integer piece contains the number of days since January 1, 1900; the second 2 byte integer holds the number of minutes since midnight.
Most, if not all, applications need and manage date and time variables in their database. Since data from applications and external sources may come in many formats, you need to know how to insert these dispersant values into DATETIME and SMALLDATETIME columns.
SQL Server has a number of default formats it expects raw date and time data to be in when inserting dates into a DATETIME, or SMALLDATETIME columns. If you attempt to insert a date that does not match one of the default date formats, then SQL Server will reject the date/time value. If your raw data does match one of the default formats, SQL Server will automatically convert your raw data into a DATETIME value without any special consideration.
Let's review some TSQL code for inserting dates and times into SQL Server. Here is a sample script that inserts the same date 10/30/1956 into a sample table using a number of different string formats.
DROP TABLE X
GO
SET NOCOUNT ON
CREATE TABLE X(D DATETIME)
INSERT INTO X VALUES ('19561030')
INSERT INTO X VALUES ('561030')
INSERT INTO X VALUES ('10/30/1956')
INSERT INTO X VALUES ('10/30/56')
INSERT INTO X VALUES ('30 OCT 1956')
INSERT INTO X VALUES ('30 OCT 56')
INSERT INTO X VALUES ('OCT 30 1956')
INSERT INTO X VALUES ('OCT 30, 1956')
INSERT INTO X VALUES ('OCT 30, 56')
INSERT INTO X VALUES ('OCTOBER 10, 1956')
SELECT * FROM X
|
As you can see from this example, not all dates specified contain a 4 digit year. SQL Server has a two-digit year cutoff option that makes this possible. My SQL Server uses the default setting which is 2049. Meaning if the year looks to be from 00-49, SQL Server will assume the first two digits of the year will be 20. In my example, the year was greater than 49, so SQL Server set the year to 1956. The two digit year cutoff is a configurable option.
Displaying Dates and Times in Different Formats
In my first article in this series, I discussed different aspects of entering date/time data into SQL Server DATETIME and SMALLDATE columns. This article will expand my discussion of date/time data by exploring how to use different SQL Server functions to display dates and times in different formats.
Depending on your environment, your needs, and/or the audience of your application, the format for displaying date and time might vary. Internationally we have many difference ways to represent a given date and/or time. Here are a few examples of different ways we might display the date January 22, 2003 with or without a time of 10:31 PM.
- 2003/01/22 10:31PM
- 2003/01/22 22:31
- 22-01-2003
- 22 January 2003
- Jan 22 2003 10:31PM
- January 22, 2003
CONVERT ( data_type [ ( length) ] , expression [ , style ] )
Where data_type [(length)] is the target data type format and length, expression is any valid Microsoft expression that represents the date/time you want to display, and style specifies the output format for the data/time.
Using the CONVERT function, with different styles, allows you to display date and time data in many different formats. Let's look at what I am taking about. The easiest way to demonstrate how to use the CONVERT function is to review some TSQL code that displays the current time in a few different display formats. The following script uses only the CONVERT function to display the different formats.
PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +
CONVERT(CHAR(19),GETDATE())
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' +
CONVERT(CHAR(8),GETDATE(),10)
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' +
CONVERT(CHAR(10),GETDATE(),110)
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' +
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' +
CONVERT(CHAR(9),GETDATE(),6)
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' +
CONVERT(CHAR(24),GETDATE(),113)
Here is the output from the above script:
1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb 5 2003 5:54AM
2) HERE IS MM-DD-YY FORMAT ==>02-05-03
3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003
4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003
5) HERE IS DD MON YY FORMAT ==>05 Feb 03
6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567
As you can see, this script displays the current date in many different formats. Some formats have two digit years, while others have four digit years. Some displays have 24 hour or AM/PM time formats. Still others have the month displayed as a numeric value, while others have a month abbreviation. Some of the displays also have the date displayed in DD MON YYYY format. I suggest you review Books Online for a description of all the formats that the CONVERT function can display using different styles.
Even though the CONVERT function has a number of data/time output styles, it still might not have the exact display format you need to display. Sometimes you will need to use other TSQL functions as well, to get the display format you desire.
Another important date/time function worth describing is DATEPART. This function will take a date/time expression and return a single part of the date, such as hour, minute, month, day, etc. A call to this function has the following form:
DATEPART(datepart, date)>
Where
the datepart is one of the following: Year, yy, yyyy, quarter, qq, q,
month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, weekday, dw,hour, hh,
minute, mi, n, second, ss, s, millisecond, or ms. And date is a valid
date expression. This function will return an integer representing the
particular date part requested.Let's review how the CONVERT and/or DATEPART functions can be used to display January 22, 2003 with a time of 10:31 PM to meet all of the display formats I showed at the top of this article.
Some of the formats can be created using the different "style" options on the CONVERT statement. Although a number of these formats above will not only require the CONVERT and/or DATEPART functions, but other TSQL functions like SUBSTRING, RIGHT, and CAST to build the desired display format. If you are unfamiliar with these additional functions, read Books Online for more information. To show you how to create each of these display formats I will build a simple script for each of the different formats.
Searching for Particular Date Values and Ranges
All applications need to retrieve data in SQL Server tables based on DATETIME and/or SMALLDATETIME columns. In your particular application, you may need to select records that were entered on a particular date. On the other hand, you might need to select a set of records that have a DATETIME column value for a particular month, or year. In other case, you might want to find all the records between two different dates. Possibly, you might need to find the first, or last record entered in a given month, day, or year. This article will discuss selecting records from a database table based on values in a DATETIME, or SMALLDATETIME column.
Prior to discussing selecting records for a particular DATETIME value, let's review what specific values are stored in a given DATETIME and SMALLDATETIME column. From my first article in this series you should recall that a DATETIME column contains a date and time value, where time is accurate to milliseconds and SMALLDATETIME columns hold a date and time value, but the time portion is only accurate to one minute. Since these date/time columns store the time portion you will need to consider this when searching for records where the column holds a specific date. You will need to provide the date and time portion in the search criteria or you may not return any records or the records you wish to return. If you are not sure of the exact time associated with the records you want to retrieve you should search based on a date and/or time range. Let's go through a couple of examples to show you what I am talking about.
DATE_SAMPLE Table
In order to show you different methods of searching SQL Server tables, I will need a sample table. The table I will be using is a very simple table called DATE_SAMPLE and here is a list of records in that table.RECORD
-------------------
1
2
3
4
5
6
7
8
9
10
|
SAMPLE_DATE
-------------------------------------
2001-11-08 00:00:00.000
2002-04-08 16:00:00.000
2003-04-12 16:59:00.000
2003-04-09 00:00:00.000
2003-04-09 08:00:00.000
2003-04-09 14:58:00.000
2003-04-09 23:59:00.997
2003-04-10 00:00:00.000
2003-04-12 00:00:00.000
2003-05-10 00:00:00.000
|
Common Mistakes When Searching for Dates:
When searching for dates there are a number of common mistakes that new SQL Server programmers sometimes make. In this section, I will show you two common date/time pitfalls.The intent of this first example is to select all the records in the DATE_SAMPLE table that have a SAMPLE_DATE equal to '2003-04-09'. Here is the code:
SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE = '2003-04-09'
|
When this code is run only record 4 is returned. Why are records 5, 6 & 7 not returned? Can you tell why? Remember DATETIME, or SMALLDATE columns contain not only the date but also the time. In this particular example SAMPLE_DATE is a DATETIME column, so all the dates store contain a time, down to the milliseconds. When you specify a search criteria that only contains a date, like the above example, SQL Server needs to first convert the string expression '2003-04-09' to a date and time value, prior to matching the string with the values in the SAMPLE_DATE column. This conversion creates a value of '2003-04-09 00:00:00.000', which matches with only record 4.
Another common mistake is to use the BETWEEN verb like so:
SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09'
AND '2003-04-10'
|
When using the BETWEEN verb all records that are between or equal to the dates specified are returned. Now if in my example above I only wanted to return records that have a SAMPLE_DATE in '2003-04-09'. This example returns all the records that have a SAMPLE_DATE in '2003-04-09' (records 4 - 7), but also returns record 8 that has a SAMPLE_DATE of '2003-04-10'. Since the BETWEEN clause is inclusive of the two dates specified, record 8 is also returned.
Now if you really desire to select all the records in the DATE_SAMPLE table that have a SAMPLE_DATE sometime in '2003-04-09' you have a couple of options. Let me go through each option and then explain why one might be better than another might.
Using the Convert Function:
This first example selects all records from the DATE_SAMPLE where the date portion of the SAMPLE_DATE is equal to '2003-04-09'.
SELECT * FROM DATE_SAMPLE
WHERE CONVERT(CHAR(10),SAMPLE_DATE,120) = '2003-04-09'
|
The reason this example works, and the first example above does not, is because this example removes the time portion of the SAMPLE_DATE column prior to the comparison with string '2003-04-09' being performed. The CONVERT function removes the time portion by truncating the value of the SAMPLE_DATE field to only the first 10 characters.
Post By Gregory A. Larsen
No comments :
Post a Comment