where statement getdate issue - SQL Server Forums

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)

where statement getdate issue

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-07-02 : 11:20:40

Hello I am having issues with trying to get this where statement to work without having to go in every day and add -number behind getdate.

Any way I can fix this to auto number. I marked them blue.

WHERE (dbo.equipment.[prod-type] <> '') AND (CONVERT(VARCHAR(10), dbo.[sa-hist].[rnu-date], 101) IS NULL) AND (CONVERT(VARCHAR(10), dbo.[sa-hist].[exp-date], 101)IS NULL) AND (CONVERT(VARCHAR(10), dbo.sa.[cancel-date], 101) IS NULL)

OR
(dbo.equipment.[prod-type] <> '') AND (CONVERT(VARCHAR(10), dbo.[sa-hist].[rnu-date], 101) <= GETDATE()) AND (CONVERT(VARCHAR(10),dbo.[sa-hist].[exp-date], 101) >= GETDATE() - 3) AND(CONVERT(VARCHAR(10), dbo.sa.[cancel-date], 101) IS NULL)
OR
(CONVERT(VARCHAR(10), dbo.[sa-hist].[rnu-date], 101) <= GETDATE()) AND (CONVERT(VARCHAR(10), dbo.[sa-hist].[exp-date], 101) >= GETDATE() - 3) AND(CONVERT(VARCHAR(10), dbo.sa.[cancel-date], 101) > GETDATE() - 3)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-02 : 11:36:59

If it is -3 today, what do you change it to tomorrow? And the day after that? In other words, what is the logic you are trying to implement?

skc40
Starting Member

34 Posts

Posted - 2014-07-02 : 11:41:58

Below should work!!

WHERE
(
(dbo.equipment.[prod-type] <> '')
AND (dbo.[sa-hist].[rnu-date] IS NULL)
AND (dbo.[sa-hist].[exp-date] IS NULL)
AND (dbo.sa.[cancel-date] IS NULL)
)
OR
(
(dbo.equipment.[prod-type] <> '')
AND (CONVERT(VARCHAR(10), dbo.[sa-hist].[rnu-date], 101) <= Convert(varchar(10),GETDATE(),101))
AND (CONVERT(VARCHAR(10),dbo.[sa-hist].[exp-date], 101) >= Convert(varchar(10),DateAdd(DD,-1,GETDATE(),101)))
AND (dbo.sa.[cancel-date] IS NULL)
)
OR
(
(CONVERT(VARCHAR(10), dbo.[sa-hist].[rnu-date], 101) <= Convert(varchar(10),GETDATE(),101))
AND (CONVERT(VARCHAR(10), dbo.[sa-hist].[exp-date], 101) >= Convert(varchar(10),DateAdd(DD,-1,GETDATE(),101)))
AND(CONVERT(VARCHAR(10), dbo.sa.[cancel-date], 101) > Convert(varchar(10),DateAdd(DD,-1,GETDATE(),101))
)

skc40
Starting Member

34 Posts

Posted - 2014-07-02 : 11:45:04

Change this from DateAdd(DD,-1,GETDATE() to DateAdd(DD,-3,GETDATE()

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-07-02 : 11:46:01

says the dateadd function requires 3 arguments

skc40
Starting Member

34 Posts

Posted - 2014-07-02 : 11:54:03

missing parentheses :(
DateAdd(DD,-3,GETDATE())

CONVERT(VARCHAR(10),DATEADD(DD,-3,GETDATE()),101)

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-07-02 : 11:54:19

will I need to go in and change that to -4 tomorrow?

skc40
Starting Member

34 Posts

Posted - 2014-07-02 : 12:00:52

No, you don't have to change that to 4 days as GETDATE() generates current date.
see --http://msdn.microsoft.com/en-us/library/ms186819.aspx for more details

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-07-02 : 12:12:53

so since we placed that -3 does that mean it will subtract 3 days at a time from today's date? I'm sorry just trying to fully understand I was looking at the site and this is why I was asking.

C. Using expressions as arguments for the number and date parameters

The following examples use different types of expressions as arguments for the number and date parameters.
Specifying a column as date

The following example adds 2 days to each value in the OrderDate column to derive a new column named PromisedShipDate.

USE AdventureWorks2012;
GO
SELECT SalesOrderID
,OrderDate
,DATEADD(day,2,OrderDate) AS PromisedShipDate
FROM Sales.SalesOrderHeader;

skc40
Starting Member

34 Posts

Posted - 2014-07-02 : 12:27:33

Yes, you're right. Subtract (-) subtracts number of days/month specified in DATEADD Function.

DATEADD (datepart,number,date )
DD 3 GETDATE() --Adds 3 days to current date
DD -3 GETDATE() --Subtracts 3 days from current date

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-02 : 12:45:53

If the only thing you are trying to do is test against a fixed date i.e., >= GETDATE() - 3 as of today, couldn't you simply hard-code the date, as in >= '20140629'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources