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.

hey, i've got many parts that don't work as i think they must ;) — slashmais, Oct 09 '10 at 18:46

score 1 · Answer 1 · edited May 23 '17 at 12:04

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).

score 1 · Accepted Answer · answered Oct 09 '10 at 18:35

1

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())

answered Oct 09 '10 at 18:35

Mark Byers

811,555
193
1,581
1,452

For anyone new to SQL Server 2008, this might look a bit confusing. Possibly worth pointing out that GETDATE() returns a DATETIME datatype. CONVERTing it to DATE truncates the time portion. – Andrew Carmichael Oct 11 '10 at 07:55

score 1 · Answer 3 · answered Oct 09 '10 at 18:35

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 :-)

score 1 · Answer 4 · edited Nov 21 '12 at 09:54

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 Departure 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())))

score 1 · Answer 5 · answered Oct 09 '10 at 18:38

1

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)

answered Oct 09 '10 at 18:38

Martijn

11,964
12
50
96

DATEPART not working like i think it must

5 Answers5