CODEWITHSUNDEEP
sql-server
Alon Shmiel
Alon Shmiel

Reputation: 7121

select all date that is lesser than today

I have a table (mytable) that contains:

id, date

and some rows (the Id is for the example):

4235          null
3563   2013-11-27 08:02:53.917
3143   2013-11-27 01:04:51.917
1455          null
5223   2013-11-26 08:02:53.917
2123   2013-11-25 08:02:53.917

I want to select all the rows that their date before today or the date is null.

so in my example, if I run the query on 2013-11-27 I want to get:

4235          null
1455          null
5223   2013-11-26 08:02:53.917
2123   2013-11-25 08:02:53.917

I think to do it with:

select case when
(
(select DATEPART(yyyy,dailybudgetexceeded) from sensplit_commercial_goal where 
commercialGoalId = 'dbe20d71-e304-4989-8524-5feef61d32a7') >= YEAR(GETDATE()) 
or...

but maybe there is a shorter way.

any help appreciated!

Upvotes: 10

Views: 42344

Answers (3)

kingkong0924
kingkong0924

Reputation: 301

SELECT *
FROM sensplit_commercial_goal
WHERE dailybudgetexceeded  < CONVERT(date, GETDATE()) OR dailybudgetexceeded  IS NULL

Upvotes: 4

Raj
Raj

Reputation: 10843

select * from mytable
where [date] < Convert(date, getdate())  or [date] is null

SQLFiddle Demo

Upvotes: 14

juergen d
juergen d

Reputation: 204766

select * 
from sensplit_commercial_goal
where commercialGoalId = 'dbe20d71-e304-4989-8524-5feef61d32a7'
and (dailybudgetexceeded < getdate() or dailybudgetexceeded is null)

Upvotes: 11

Related Questions