DATEPART not working like i think it must

Question

select * 
  from Advertisements 
 where DepartureDate < DATEPART('dd.mm.yy', '09.10.2010');

but i get

Msg 1023, Level 15, State 1, Line 1 Invalid parameter 1 specified for datepart.

in plsql is this very simple here is so complicated... Can someone tell me please how can i get all dates that are smaller than today.

Answer 1

You seem to be confusing DATEPART with FORMAT_DATE (which does not exist anyway).

DATEPART extracts certain part of a date. Exactly one part.

Dates that are smaller than today are < dbo.CropTime(getdate()), where CropTime is a function which can be written in different ways (such as those described in this question).

Or, in case you are using SQL Server 2008, it's as simple as < cast(getdate() as date).

Answer 2

You can use this to get the current date:

CONVERT(date, GETDATE())

See the documentation.

Can someone tell me please how can i get all dates that are smaller than today.

SELECT * 
FROM Advertisements 
WHERE DepartureDate < CONVERT(date, GETDATE())

Answer 3

Would that code really work in PL/SQL? The DATEPART in T-SQL function is used to extract individual portions of a date.

This will get you all the dates before now.

select * from Advertisements where DepartureDate < getdate()

If you're planning to hardcode the date (as your sample code suggests), you just need to format in a way that SQL Server will understand. eg.

select * from Advertisements where DepartureDate < '2010-10-09'

I've been told that date format works on every server regardless of its localization settings. It's certainly worked on every server I've tried it on - but I'm happy to be overruled :-)

Answer 4

DatePart is used for getting components of the date such as the month, year or day. To get dates that are smaller (older) than now I would do this.

select * from Advertisements where DepartureDate < GetDate();

If I wanted Departtures dates that were yesterday or before I could do this.

select * from Advertisements where DepartureDate < Convert(DateTime,Convert(Char(10),GetDate(),121));

or

select * from Advertisements where DepartureDate < Convert(DateTime,floor(convert(int,GetDate())))

Answer 5

What you are looking for I think is

select * 
from Advertisements 
where DepartureDate < Convert(Date, '09.10.2010', 102)

or possibly

SELECT *
FROM Advertisements
WHERE DepartureDate < Cast(CURRENT_TIMESTAMP as date)