CASE statement with GETDATE()

sql sql-server-2008 case getdate
CSF90 picture CSF90 · Oct 3, 2013 · Viewed 9.6k times · Source

Wondering if you can help a little with the syntax here. Trying to set a variable as a month value dependent upon whether it is past the 25th day of the month.

If it is, then it uses the current month (e.g. the variable 'month' will be 10 if the date is 28th October, but will be 9 if it's the 24th October). So far I've got the following:

select a
case
    when (SELECT DAY(GETDATE()) >= 25
    then a = (SELECT MONTH(GETDATE()))
    else a = (SELECT MONTH(GETDATE()) - 1)
end

I understand you can't use less than or greater than signs, as case statements are only for evaluations (=)? Can anyone suggest another way of doing this?

Thanks.

Answer

juergen d picture juergen d · Oct 3, 2013 
select @your_variable = case when DAY(GETDATE()) = 25
                             then MONTH(GETDATE())
                             else MONTH(GETDATE()) - 1
                        end

Related questions

TSQL CASE with if comparison in SELECT statement

I would like to use CASE statement in SELECT. I select from user table, and (as one attribute) I also use nested SQL: SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber, hobbies, ... FROM USERS and then …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More