One of the most commonly asked questions, I have seen across varying forums and newsgroups pertains to using or calculating date values. The most important thing to remember when working with dates is to not use functions in the predicate. Using functions in the predicate prevents the optimizer from using an index to seek the row. The optimizer cannot seek the row because the function makes the where clause non-SARGable. So what does this fancy lingo mean? Well…. it means the where clause does not have a valid search argument, so the optimizer has to scan the table/index. An example is presented below.
SELECT * FROM MyTable WHERE MONTH(MyDateCol) = MONTH(GetDate()) AND YEAR(MyDateCol) = YEAR(GetDate())
The query should be written as below, which has a valid SARGable predicate.
SELECT * FROM MyTable WHERE MyDateColumn >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AND MyDateColumn < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)
The code above tells the database engine to return all rows that have date greater than or equal to the first day of the current month and less than the first day of the next month. You should try to use the >= and < signs when working with dates because time can render your results incorrect. This type of search yields better performance and allows the optimizer to use indices.
Now let’s get to the useful stuff :)
DECLARE @DATE AS DateTime SET @DATE = GETDATE() --common date functions SELECT DATEADD(DAY,DATEDIFF(DAY,0,@DATE),0) AS DayStart, DATEADD(SECOND,-1,DATEADD(DAY,DATEDIFF(DAY,0,@DATE)+1,0) ) AS DayEnd, DATEADD(week,DATEDIFF(week,0,@DATE),0) AS WeekStart, DATEADD(SECOND,-1,DATEADD(week,DATEDIFF(week,0,@DATE)+1,0) ) AS WeedEnd, DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE),0) AS MonthStart, DATEADD(SECOND,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE)+1,0) ) AS MonthEnd, DATEADD(YEAR,DATEDIFF(YEAR,0,@DATE),0) AS YearStart, DATEADD(SECOND,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@DATE)+1,0) ) AS YearEnd , DATEADD(QUARTER,DATEDIFF(QUARTER,0,@DATE),0) AS FirstDayQtr, DATEADD(SECOND,-1,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@DATE)+1,0)) AS LastDayOfQuarter --calculate age DECLARE @birthdate DATETIME SET @birthdate='1982-09-22' SELECT CONVERT(VARCHAR,YEAR(GETDATE()-@birthdate)-1900)+' Years '+ CONVERT(VARCHAR,MONTH(GETDATE()-@birthdate))+' Months '+ CONVERT(VARCHAR,DAY(GETDATE()-@birthdate))+' Days'
SELECT DATEDIFF( MONTH, CASE WHEN DAY(@birthdate) > DAY(GETDATE()) THEN DATEADD(MONTH,1,@birthdate) ELSE @birthdate END, GETDATE() ) AS 'Tot_Months', DATEDIFF( MONTH, CASE WHEN DAY(@birthdate) > DAY(GETDATE()) THEN DATEADD(MONTH,1,@birthdate) ELSE @birthdate END, GETDATE() ) / 12 AS YearsOld --calculate leap year DECLARE @year INT SET @year = 2008 SELECT CASE WHEN ( @year & 3 ) > 0 THEN 0 -- Anything with 1st or 2nd bits set is not a leap year WHEN ( @year % 400 ) = 0 THEN 1 -- Anything divisible by 400 is a leap year WHEN ( @year % 100 ) = 0 AND ( @year % 400 ) > 0 THEN 0 -- 100-year rule WHEN ( @year % 4 ) = 0 THEN 1 --when divisible by 4 then leap year ELSE 0 --else not a leap year END
SELECT CASE WHEN DAY(DATEADD(DAY,-1,DATEADD(MONTH,((@year-1900)*12)+2,0))) = 29 THEN 1 ELSE 0 END
So there you go.. You now have the most commonly requested date functions and an example of how to use it.
2 comments:
Hi Adam...
Don't know about performance, but for the leap year test, we could just let SQL tell us:
select case when day(dateadd(month,2+12*(@year-1900),0)-1)=29 then 1 else 0 end
Given the year, get to March 1 of that year, subtract 1 day, and see if it's the 29th of the month.
I have heard about another way of fix crashed dbf database. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues
Post a Comment