How to use Date Diff Function?

Question

Post reply

How to use Date Diff Function?

gouri92

SSC Veteran

Points: 259
More actions
May 2, 2015 at 12:55 am

#297763

Hi,
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result please help me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Quick suggestion, use the GETDATE() function in T-SQL

😎

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 2

gouri92 (5/2/2015)
Hi,
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result please help me.

I almost never say never but you should almost never include a column name in a function in a WHERE clause because it makes it impossible to do an index seek unless several other factors come into play.

SELECT Name

,Profession

,ProfileCreationDate

FROM dbo.tblRegistration

WHERE ProfileCreationDate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-7,0)

ORDER BY ProfileCreationDate DESC

;

The DATEDIFF, in this case, calculates the number of whole days (midnight time, also known as a "Date Serial Number")) since the 1900-01-01 (the "0" base date which is what the 0's in the formula are) and subtracts 7 from that. The DATEADD isn't actually necessary when the ProfileCreationDate is a DATETIME because the number of days will correctly be converted back to a DATETIME but the purists in the group would insist that you can't count on the column being a DATETIME. So the DATEADD explicitly converts the Date Serial Number created by the DATEDIFF-7 back to a DATETIME.

Notice that the column name is NOT inside the formula, which means that it could be used to do an index seek. That's known as being SARGable. The "SARG" in that term means "Search ARGument".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

Eirikur Eiriksson (5/2/2015)
Quick suggestion, use the GETDATE() function in T-SQL
😎

Heh... it wouldn't be "quick" though because it wouldn't be SARGable if the same formula the OP used were simply modified to use GETDATE(). 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Jeff Moden (5/2/2015)
Eirikur Eiriksson (5/2/2015)
Quick suggestion, use the GETDATE() function in T-SQL
😎
Heh... it wouldn't be "quick" though because it wouldn't be SARGable if the same formula the OP used were simply modified to use GETDATE(). 😉

Should have said "quick and incomplete suggestion", the phone went bonkers on me before I could finish:-P

Was of course referring to System.DateTime.Now

😎