SQL Server 2000 does not have separate data types for date and time. Instead the Microsoft SQL Server Team chose to combine both the data types into one and store it as a datetime data type. Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute.
SQL Server takes into account a system reference date, which is called the base date for SQL Server. This base date is January 1st, 1900. It is from here that the main problem stems. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight.
When only the time part is provided as input, the base date is appended to the time. If only the date part is provided the time appended is as of midnight. Some example code to observe the same is as follows:
use pubs go ---------------- Inserting only the time part into a datetime column -------------- /* Inserting the test value into the table */ /* Selecting the result */ /* Performing Cleanup */ ---------------- Inserting only the date part into a datetime column -------------- go /* Creating a Test Table */ /* Inserting the test value into the table */ /* Selecting the result */ /* Performing Cleanup */ |
Q: How do I get SQL Server to return only the Date component or only the Time component from the datetime data type?
A: By using the Convert function. The syntax for using the convert function is:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) |
By varying the datatype and length, we can get the desired component. Moreover, the style argument in the Convert function is provided exclusively for use with date and time data. Some sample code illustrating the same is as follows:
use pubs go ---------------- Selecting only the date part from a datetime column -------------- /* Inserting the test value into the table */ /* Selecting the result */ /* Performing Cleanup */ use pubs go ---------------- Selecting only the date part from a datetime column -------------- /* Inserting the test value into the table */ /* Selecting the result */ /* Performing Cleanup */ |
The list of styles that can be used are:
Style ID | Style Type |
---|---|
0 or 100 | mon dd yyyy hh:miAM (or PM) |
101 | mm/dd/yy |
102 | yy.mm.dd |
103 | dd/mm/yy |
104 | dd.mm.yy |
105 | dd-mm-yy |
106 | dd mon yy |
107 | Mon dd, yy |
108 | hh:mm:ss |
9 or 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
110 | mm-dd-yy |
111 | yy/mm/dd |
112 | yymmdd |
13 or 113 | dd mon yyyy hh:mm:ss:mmm(24h) |
114 | hh:mi:ss:mmm(24h) |
20 or 120 | yyyy-mm-dd hh:mi:ss(24h) |
21 or 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
126 | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
130 | dd mon yyyy hh:mi:ss:mmmAM |
131 | dd/mm/yy hh:mi:ss:mmmAM |
These styles are the format of input to be used when converting character data into datetime and format of output while converting datetime data into characters:
use pubs go ---------------- Example for the demonstration of use of style while input of data--------------
/* Selecting the result */ /* Performing Cleanup */ use pubs go ---------------- Example for the demonstration of use of style while output of data-------------- /* Creating a Test Table */ go /* Selecting the result */ go |
Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:
Dateadd: Returns a new datetime value based on adding an interval to the specified date.
Syntax: DATEADD ( datepart, number, date )
Datediff: Returns the number of date and time boundaries crossed between two specified dates.
Syntax: DATEDIFF ( datepart, startdate, enddate )
Datename: Returns a character string representing the specified datepart of the specified date.
Syntax: DATENAME ( datepart, date )
Datepart: Returns an integer representing the specified datepart of the specified date.
Syntax: DATEPART ( datepart, date )
Day: Returns an integer representing the day datepart of the specified date.
Syntax: DAY ( date )
Getdate: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.
Syntax: GETDATE ( )
Month: Returns an integer that represents the month part of a specified date.
Syntax: MONTH ( date )
Year: Returns an integer that represents the year part of a specified date.
Syntax: YEAR ( date )
declare @datevar datetime /*Example for getdate() : getting current datetime*/ /*Example for dateadd : getting date 7 days from current datetime*/ /*Example for datediff : getting no of days passed since 01-01-2004*/ /*Example for datename : getting month name*/ /*Example for datepart : getting week from date*/ /*Example for day : getting day part of date*/ /*Example for month : getting month part of date*/ /*Example for year : getting year part of date*/ |
Now I will provide you with some code samples which you can use for various tasks. I will try to include as many examples I can think of, but this list is not exhaustive:
1. To find the first day of a month:
select dateadd(dd,-(day(DateColumn)-1),DateColumn) |
2. To find last day of a month:
select dateadd(dd,-(day(dateadd(mm,1,DateColumn))),dateadd(mm,1,DateColumn)) |
3. To find birthdays in next seven days:
use pubs go /* Creating a Test Table */
|
4. Number of hours until weekend, that is until Friday at 5 PM (my favorite):
use pubs go Create function udf_Time_to_Weekend (@d1 datetime) returns datetime |
5. First and last days of quarter, in which a date falls:
use pubs |
6. Number of days in a month:
Create Function go select dbo.udf_getNoOfDaysInMonth(2,2004) go |
A very common question asked in forums is regarding the change from a character column to a datetime column. The error encountered by developers is :
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
This is common because the varchar column does not provide any validations against the data and as a result, some invalid entries creep in. So, while converting to datetime, SQL Server is not able to change the character data to datetime and throws up an error. The easiest way to identify the rows that are causing problems and contain invalid datetime data is by using the isdate() function:
/* Example to show how to find invalid records */ use pubs |
Another common mistake made by developers is that while searching for all records on a particular day a where clause is used like "where logdate = @logdate", when they are passing @logdate as '01/01/2004'. '01/01/2004' really means '01/01/2004 00:00:00.000' and will not return data for the complete day. The problem can be solved by using the between clause. The where clause for such a query should be "where logdate between @logdate and @logdate2", where @logdate2 is @logdate + 1. The between clause can make use of an index if it exists, where using a convert function like "where convert(varchar,logdate,101) = @logdate" would not and it would slow down the query.
No comments:
Post a Comment