Hello,
Yes I tried searching first but its hard for me to put this question into a search query form I did try though!
Two questions:
1:
I’m trying to figure out how to use a formula or some kind of if/then logic within a query. For example I have the condition in my query to say the date of the comment is > 1/1/2011 but instead i would rather have the current date - 90 days. I tried writing the SQL to say DATEADD(day, -90, CURRENT_TIMESTAMP) I just keep getting errors about DateAdd is not proper usage. I also tried Current Date - 90 Days and the sql passed but it really didn’t work.
[EDIT]
I believe i fixed this piece by using current timestamp - 90 days
Still working on #2
2:
Also trying to build something similar to say if today’s date is Monday then use current date - 2 days else current date - 1 day.
Let me know if this makes sense I’ve been battling this all day so any help is greatly appreciated.
Select
bde.obejct1.code
bde.object2.date,
From
bde.folder1,
bde.folder2
Where
(
bde.object1.code = ‘ABC’
and
bde.object2.date = -previously had an exact date
Case trying a case statement, passes sql but returns no results
When day(current timestamp) = ‘1’ Then (Current timestamp - 4 days)
Else
(Current Timestamp - 2 days)
End
)
FOR FETCH ONLY WITH UR
I know i’m missing something i’m used to saying if day = monday then bla bla
Thanks guys.
So just clarification of what i’m trying to do:
Instead of having the object in the query = an exact date i need it to = the prior day, but if today is monday i need it to = friday and saturdays date.
What does “current timestamp” return? I suspect a Date with a timestamp?
What does bde.object2.date return a date with a time stamp, or without?
Obviously, if one has a time stamp and one doesn’t you aren’t going to get equality.
Maybe you should look at datediff and getdate()? Thats why I posted the link…
I was doin some google searches and someone mentioned to use current_timestamp i thought it was similar to getdate(). The bde.object1 returns a date with a time stamp for example 4/5/11 12:46 PM.
When I tried cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) from the link you provided(which is awesome quick info thanks!) it gave me an error “D” is not valid in the context where it is used.
There are or can be differences between current_timestamp and getdate(), I’m not going through them here.
When I haven’t had a calendar table and I have been working with SQL Server, in the past, I have always used getdate(), for reference to todays date.
From what you have given me, you need to format both sides of your date, DB date field and getdate() to remove the time portion, then do your = to do your comparison.
(BOB member since 2010-08-11)
(BOB member since 2005-01-06)