minus a date

[Shin25]

Hi All

can anyone tell me how in ms sql 2005 do I put a where clause in which only returns records which <= current date ?

Many Thanks

 

[gmmastros]

Where DateColumn <= GetDate()

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Shin25]

Hi thanks for the above but I dont understand the getdate, I keep getting error
'The getdate function requires 0 arguments.'

 

[gmmastros]

That is because you put something between the parenthesis.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Shin25]

Many thanks for that, what if I wanted to define a date e.g.
2004,01,01 ....how would I construct that query...

I really appreciate your help....

 

[gmmastros]

[tt][blue]Where DateColumn < '20040101'[/blue][/tt]

The part between the single-quotes should be formatted as yyyymmdd. This is considered the ISO-Unseparated data format. For example, today, March 12, 2008 would be '20080312'.





-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Shin25]

once again thanks...lastly how do I construct a query which looks at a date field and minus 14 days for e.g. in a where clause

datecolumn < = (datecolumn - 14days)

Many Thanks

 

[gmmastros]

Where DateColumn <= SomeDate - 14

You can simply subtract from a date value if you want to subtract days. If you want to subtract some other units (Ex: Months) then you would use DateAdd.

Ex:

Where DateColumn <= DateAdd(Month, -1, SomeDateValue)

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Shin25]

would that be the same if I wanted to subtract from the date field and have a new field showing this for example

where datecolumn - 14 days ....show as field new_date

thanks

 

[gmmastros]

If I understand correctly, you would want to put that in the SELECT clause.

[tt][blue]
Select DateColumn-14 As New_Date
From TableName
Where Whatever....
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Shin25]

this is what I have done

select *
from dbo.sales
--order by ord_date desc

Where ord_date <= 1995-02-01 - 14

froms the pubs db in sql.... this should bring back all 21 records as all ord_date in the table <= to the date 1995-02-01

 

[Shin25]

sorry did not add, with the query above I am getting no records back.......abit confused...

cheers

 

[Shin25]

Anyone any ideas????

 

[Crowley16]

erm, you can't combine a date varchar with the subtract and without delimiters...

you should be doing:

...
where ord_date <= dateadd(d, -14, '1995-02-01')

--------------------
Procrastinate Now!

 

[r937]

change this --

ord_date <= 1995-02-01 - 14

to this --

ord_date <= '1995-02-01' - 14


r937.com | rudy.ca