Strange thing happening when I enter my dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Strange thing happening when I enter my dates

Author

Topic

g3jimha
Starting Member

10 Posts

Posted - 2011-06-17 : 09:05:41

Hi there, im currently doing a project and I am looking to show the details of a card thats due to expire in the next month.I have, so far got;

Select *
From SSSTS
Where DateAdd(Month,6,SSSTSExpiryDate) > DateAdd(Month,6, getDate())

This does return values and doesnt show any that are nulls which makes me think im close to the solution but it just returns all values that arent nulls. And appears as though it is not taking into consideration the year, I have also tried using the between statement for the dates but that, likewise, returns similar results. just wondering if anyone could help or direct me to somewhere on the internet that could help. Thanks, Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-17 : 09:11:56

Select *
From SSSTS
Where SSSTSExpiryDate > dateadd(day,datediff(day,0,getdate()),0)
and SSSTSExpiryDate <= dateadd(day,datediff(day,0,dateadd(month,6,getdate())),0)

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 09:12:59

Expire in the next month

Select *
From SSSTS
Where SSSTSExpiryDate <= DateAdd(Month,1, getDate())
and SSSTSExpiryDate >= getDate()

Select *
From SSSTS
Where SSSTSExpiryDate getween getdate() and DateAdd(mm,1, getDate())

you might want dateadd(dd,(datediff(dd,0,getdate()),0)
instead of getdate() to ignore the time of day.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

g3jimha
Starting Member

10 Posts

Posted - 2011-06-20 : 03:52:06

Thanks very much for the speedy replys and both work so thanks again :)
Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources