CODEWITHSUNDEEP
sqlsql-serverdategetdatedatepart
SonOfJorEl
SonOfJorEl

Reputation: 23

How to use DATEPART of the GETDATE as a variable for a query

This might be easier than I'm making it, I'm pretty new to SQL.

I have a database with a lot of movies release dates in the format YYYY-MM-DD.

I'm trying to write a query that would return films released on the current date (MM-DD) in various years.

I've tried using:

WHERE ReleaseDate LIKE '%-(DATEPART(mm,GETDATE())-(DATEPART(dd,GETDATE())'

But it doesn't return anything.

"WHERE ReleaseDate LIKE '%-06-03'" works but I'd really like to automate this.

Upvotes: 2

Views: 877

Answers (2)

Mureinik
Mureinik

Reputation: 311163

You are treating datapart etc as a string literal by putting it inside the quotes.

You could use concat to build this string dynamically:

WHERE ReleaseDate LIKE CONCAT('%-', 
                              (DATEPART(mm,GETDATE()), 
                              '-', 
                              (DATEPART(dd,GETDATE())
                             )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Arrg! Don't use LIKE with dates.

How about just doing something like this:

where DATEPART(month, ReleaseDate) = DATEPART(month(GETDATE()) AND
      DATEPART(day, ReleaseDate) = DATEPART(day, GETDATE())

Or, more simply:

where month(ReleaseDate) = month(getdate()) and
      day(ReleaseDate) = day(getdate())

Upvotes: 3

Related Questions