CODEWITHSUNDEEP
sqlsql-serverdate
Mahe
Mahe

Reputation: 1432

Getting specific date values in SQL

I need to perform a query in which a require date value to be current date(or specific date) - 7 days. I came across many solutions like these,

(SELECT CONVERT(VARCHAR(10),DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -7),120))

However, for getting a week's value, i found a similar solution from here.

SELECT CONVERT(varchar(25),PostDate,107) AS duration, count(*) AS posts
 FROM MDBDetails 
 WHERE DATEDIFF(week, PostDate,GETDATE()) = 1
 GROUP BY  CONVERT(varchar(25),PostDate,107)
 ORDER BY duration

But, a simple query like GETDATE() -7 or the below code seems to do the required job for me.

DECLARE @Date DATETIME = '12/25/13'
SELECT @Date-7

My question is that, isn't the above method of subtracting date with a numerical value a good practice? Of course I get the use of DATEDIFF can be extended to specify months and years too. But, is the above method a good practice? Is it necessary to use only DATEDIFF method to get the exact date values? Are there any major drawbacks or differences when compared to the first two methods? (except for subtracting months and years).

Upvotes: 0

Views: 222

Answers (1)

GriGrim
GriGrim

Reputation: 2921

You'd better use DATEADD function.

Because if one day you decide to change date type of your column from DATETIME or SMALLDATETIME to DATE there will be an error.

You cannot subtract days from DATE. You should only use DATEADD function instead.

Upvotes: 3

Related Questions