Case and IF clause in sql server

case statement with multiple conditions in sql server
case statement in sql server
nested case statement in sql
if...else in sql
sql case statement in where clause multiple values
multiple case when sql
sql case when multiple values
sql if...else begin end

I need to concate 0 when month and day is less than 10 (Or Single Digit value). When i am using IF statement it works, but in CASE statement it's doing an addition.

IF Statement

IF Month(getdate()) < 10
 Begin 
 select '0' + cast(Month(getdate()) as varchar(10))
 END
Else
BEGIN
  select month(getdate())
END

CASE Statement

SELECT 
CASE
    WHEN(
        CAST(Month(getdate()) as varchar(10)) < 10
    ) THEN '0' + cast(Month(getdate()) as varchar(10))
    ELSE (Month(getdate()))
END

You don't need such string manipulations. The FORMAT command introduced in SQL Server 2012 allows you to write :

select format (getdate(),'MM')

The format string is similar to .NET Format strings. Eg, if you wanted to format a number using a leading zero or optional you could use 0 or # :

select format(5,'##'),format(5,'00')

This returns 5, 05.

You can also use the standard format specifiers, eg :

select format(getdate(),'s'), format(getdate(),'u')

Return 2019-01-11T10:28:41, 2019-01-11 10:28:41Z

You tagged the question SQL Server 2017 which means you can use FORMAT without issues.

SQL Server: CASE Statement, E.g., Visitor will perform the act of visiting New York only in the condition if the flight ticket is between $100 to $200. Similarly, MS SQL CASE  The SQL CASE Statement. The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

The difference is that on the CASE you are comparing the VARCHAR result (by the cast) of the MONTH function while in the IF you are comparing it as an INT.

Just remove the cast to varchar on the comparison:

SELECT 
    CASE WHEN Month(getdate()) < 10 THEN '0' + cast(Month(getdate()) as varchar(10))
    ELSE Month(getdate())
END

Can be simplified as the following though:

SELECT RIGHT('0' + CONVERT(VARCHAR(2), MONTH(GETDATE())), 2)

CASE statement in SQL Server: T-SQL Example, This is most easily accomplished in all versions of SQL Server using the CASE statement, which acts as a logical IFTHENELSE expression and returns  CASE is the extension of IFELSE statement. Unlike IF…ELSE, where only the maximum of one condition is allowed, CASE allows the user to apply multiple conditions to perform different sets of actions in MS SQL. Let's learn this concept in detail in the following sections. In MS SQL, there are two types of CASE.

Hope that it helps.

DECLARE @MONTH AS NVARCHAR(2)

SET @MONTH = CONVERT(NVARCHAR(2), MONTH(GETDATE()))

IF(LEN(@MONTH) = 1)
    BEGIN
        SELECT '0' + @MONTH
    END

SELECT 
    CASE WHEN(LEN(@MONTH) = 1) THEN '0' + @MONTH
    ELSE
        @MONTH
    END

Good luck.

How to Use IFTHEN Logic in SQL Server, The Transact-SQL statement that follows an IF keyword and its condition is executed if the condition is satisfied: the Boolean expression returns  SELECT CASE WHEN( CAST(Month(getdate()) as varchar(10)) < 10 ) THEN '0' + cast(Month(getdate()) as varchar(10)) ELSE (Month(getdate())) END. sql-server if-statement sql-server-2017 case-statement. share. |.

IFELSE (Transact-SQL), If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL  June 28, 2019 by Ben Richardson. SQL Server CASE statement is equivalent to the IF-THEN statement in Excel. The CASE statement is used to implement the logic where you want to set the value of one column depending upon the values in other columns. The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements.

CASE (Transact-SQL), This tutorial shows you how to use the SQL Server IFELSE statement to control the flow of a program. In SQL server, To write if then else in SQL select query we can use. SELECT CASE statement (In all versions of SQL server) SELECT IIF logical function (From SQL server 2012 ) We will take an example Employee table which has columns EmpId, EmpName, Experience, Salary, Gender. Now we want to divide employees based upon their experience and salary.

SQL Server IF ELSE Statement By Examples, This article gives overview of CASE statement in SQL along with use cases In the following query, we are using a comparison operator and  I am using SQL Server 2008 R2. I want the priority based sorting for records in a table. So that I am using CASE WHEN statement in ORDER BY clause. The ORDER BY clause is as below : ORDER BY CASE WHEN TblList.PinRequestCount <> 0 THEN TblList.PinReq

Comments
  • Use FORMAT instead of trying to concatenate