1) Difference between datetime and smalldatetime.
The SMALLDATETIME data type is smaller and less precise than the DATETIME data type.
The DATETIME data type requires 8 bytes of storage, can be used to store dates
between January 1, 1753 and January 1, 9999 and has accuracy to the millisecond.
In contrast, the SMALLDATETIME data type can store dates between January 1, 1900
and January 1, 2079, and has accuracy to the minute.
In short, the SMALLDATETIME data type is adequate for most purposes unless a high
degree of accuracy is necessary.
2) Obtaining the current server time.
It is often convenient to know the current time on the server and the CURRENT_TIMESTAMP
built-in function (the GETDATE()) function is equivilant) returns a DATETIME value
with the current system time on the server.
The CURRENT_TIMESTAMP function can be used as a default value for table columns,
directly in queries to find dates older or newer than the present, in functions or selected
into variables.
3) Convert a datetime variable to a varchar.
The CONVERT function can be used to convert a SMALLDATETIME or DATETIME value to
a CHAR or VARCHAR value. The CONVERT function takes the following form, where datatype
is the type into which the DATETIME should be converted (usually VARCHAR), expression
is the DATETIME, and style is an optional numeric value from the table below:
CONVERT(datatype,expression[,style])
Converting dates can be a convient way to normalize them. Use style 101 to chop off the
time portion then convert the date back to SMALLDATETIME if you wish to aggregate rows by
day.
| style |
Output |
|
- or 0 or 100
|
Jan 15 2007 10:08PM
|
|
1
|
01/15/07
|
|
101
|
01/15/2007
|
|
2
|
07.01.15
|
|
102
|
2007.01.15
|
|
3
|
15/01/07
|
|
103
|
15/01/2007
|
|
4
|
15.01.07
|
|
104
|
15.01.2007
|
|
5
|
15-01-07
|
|
105
|
15-01-2007
|
|
6
|
15 Jan 07
|
|
106
|
15 Jan 2007
|
|
7
|
Jan 15, 07
|
|
107
|
Jan 15, 2007
|
|
8 or 24 or 108
|
22:08:00
|
|
9 or 109
|
Jan 15 2007 10:08:00:000PM
|
|
10
|
01-15-07
|
|
110
|
01-15-2007
|
|
11
|
07/01/15
|
|
111
|
2007/01/15
|
|
12
|
070115
|
|
112
|
20070115
|
|
13
|
15 Jan 2007 22:08:00:000
|
|
14
|
22:08:00:000
|
|
20
|
2007-01-15 22:08:00
|
|
21 or 25
|
2007-01-15 22:08:00.000
|
|
21 or 22
|
01/15/07 10:08:00 PM
|
|
121
|
01/15/07 10:08:00 PM
|
|
23
|
2007-01-15
|
|
126
|
2007-01-15T22:08:00
|
|
130
|
26 ?? ????? 1427 10:08:00:000PM
|
|
131
|
26/12/1427 10:08:00:000PM
|
4) Using the datepart, dateadd and datediff functions.
The DATEPART, DATEADD and DATEDIFF functions are useful functions for working with dates
or parts of dates.
The DATEPART function returns the value of the specified datepart from a date. Use
this function to retrieve the month, hour, weekday, etc. from a given date.
DATEPART(datepart,date)
The DATEADD function returns a new datetime based on adding the specified count of the
specified interval to a date.
DATEADD(datepart,number,date)
The DATEDIFF function the count of dateparts between startdate and enddate.
DATEDIFF(datepart,startdate,enddate)
All three functions accept the following datepart values
|
DatePart
|
Abbreviation
|
|
year
|
yy or yyyy
|
|
quarter
|
qq or q
|
|
month
|
mm or m
|
|
dayofyear
|
dy or y
|
|
day
|
dd or d
|
|
week
|
wk or ww
|
|
weekday
|
dw
|
|
hour
|
hh
|
|
minute
|
mi, n
|
|
second
|
ss, s
|
|
millisecond
|
ms
|
5) Find the week-beginning or week-ending of a given date.
This is a useful user defined function that returns the sunday of the week
into which a given date belongs. This function is a convienient way to group values
by week and is less complex than using DATEPART, DATEADD and the week datepart.
create function dbo.WeekBeginning(@CurDate datetime)
returns
datetime
as
begin
return
convert(
smalldatetime,
convert(varchar(25),
dateadd(day,1-datepart(weekday,@CurDate),@CurDate),107))
end
This is the same function modified to return the last day of the week (Saturday)
instead of the first.
create function dbo.WeekEnding(@CurDate datetime)
returns
datetime
as
begin
return
convert(
smalldatetime,
convert(varchar(25),
dateadd(day,1-datepart(weekday,@CurDate),@CurDate)+6,107))
end
6) Find the month-beginning or month-ending of a given date.
These simple functions return the first and last day of the month into which a given date falls.
To find the first day of the month, we determine what the current day of month is, and subtract that
number of days minus one from the current date. The convert function is used to trim off any time element
associated with the parameter date.
CREATE function dbo.BeginningOfMonth(@dtDate datetime) returns datetime
as
begin
return convert(datetime,convert(varchar(30),dateadd(day,-1 * (datepart(day,@dtDate)-1),@dtDate),1))
end
To find the last day of the month, we use the BeginningOfMonth function above, then add one month.
CREATE function dbo.EndOfMonth(@dtDate datetime) returns datetime
as
begin
return dateadd(second,-1,dateadd(month,1,dbo.BeginningOfMonth(@dtDate)))
end
7) Find which week of the month a given date falls in.
The WeekOfMonth function returns the ordinal week of month (1-5) that a given date falls in to. Again,
the BeginningOfMonth function above is used to determine the beginning of month. One is added to the result
so that the first week in the month will be "1" rather than "0".
CREATE function dbo.WeekOfMonth(@dtDate datetime) returns int
as
begin
return datediff(week,dbo.BeginningOfMonth(@dtDate),@dtDate)+1
end
8) Account for missing dates when querying for by-date trends
A problem that often presents itself when creating charts based on data summerized by date
is the issue of missing dates. For example, a chart of customers by day at a ski area where
the ski area may not be open every day at the beginning or ending of the season should include
those days when there are no customers because the ski area is closed. Otherwise the chart
might appear to show an overly optimistic trend.
One way that I have developed to work around this problem is to use a table variable
into which I have inserted all valid dates for the range to be displayed. The data is
then joined against this table assuring that there will be at least one row per date segment
even if there is no corresponding data.
Here is an example of the technique in use.
declare @CountOfWeeksToShow int
set @CountOfWeeksToShow=20
---Retrieve data into temporary table.
select
datepart(ww,a.[CreateDate]) 'iWeek',
datepart(year,a.[CreateDate]) 'iYear',
count(a.[Visitors]) ) 'Count_Of_Visitors'
into
#x
from
VisitHistory a
where
datediff(week,a.[CreateDate],current_timestamp)<@CountOfWeeksToShow
group by
datepart(ww,a.[CreateDate]),
datepart(year,a.[CreateDate])
order by
datepart(year,a.[CreateDate]),
datepart(ww,a.[CreateDate])
---Create table variable to contain all dates for given date period.
declare @weeks table(dtWeekDate datetime,iWeek int,iYear int)
declare @iCurWeek int,
@dtCurDate datetime
set @iCurWeek=0
while @iCurWeek<@CountOfWeeksToShow
begin
set @dtCurDate=dateadd(ww,@iCurWeek * -1,current_timestamp)
---See above for definition of UDF WeekBeginning
insert into @weeks(dtWeekDate,iWeek,iYear)
values(
WeekBeginning(@dtCurDate),
datepart(ww,@dtCurDate),
datepart(year,@dtCurDate))
set @iCurWeek=@iCurWeek+1
end
---Use join to return results
select
a.dtWeekDate,
b.[Count_Of_Visitors]
from
@weeks a
left outer join #x b on
b.[iYear]=a.[iYear]
and b.[iWeek]=a.[iWeek]
order by
a.dtWeekDate
drop table #x