SQLTeam.com | Weblogs | Forums

Date issue


#1

I don't understand why I am not getting "2015-09-17". It is the minimum

SELECT Min(d)
FROM (VALUES (Dateadd(day, 60, '2015-09-17')),
(LEFT(CONVERT(VARCHAR, Dateadd(day, 60, Getdate()), 120), 10))) v(d)


#2

Because you are adding 60 days to each row in your VALUES construct. You can see the values that you are trying to get the MIN of if you replace the Min(d) with d.

SELECT d 
FROM (VALUES (Dateadd(day, 60, '2015-09-17')), 
(LEFT(CONVERT(VARCHAR, Dateadd(day, 60, Getdate()), 120), 10))) v(d)

That shows

2015-11-16 00:00:00.000
2015-09-29 00:00:00.000

And the MIN is of course 2015-09-29.

By the way, even though it may not matter in this specific case, when you use VARCHAR, always remember to specify a length - for example, VARCHAR(32).


#3

For me, I need 2015-09-17 to be the min. I am guessing I coded it wrong


#4

What is the business logic that you are trying to implement?

For example, if you are given a fixed date - 2015-09-17 for example - and your requirement is to pick out the lesser of that date or current_date+60 days, then you would do the following:

SELECT MIN(d)
FROM (
	VALUES 
		( '20150917' ), 
		( CAST( DATEADD(DAY, 60, GETDATE()) AS DATE ) )
	 )v(d)

But, I am just guessing the business logic you are trying to implement. Can you describe that?


#5

Yes as you said, you coded it wrong. You should just simply use that value or do not add 60 to it