SQL date functions getdate () and case Syntax

Date functions

Definition and usage
The GETDATE () function returns the current time and date from SQL Server.

Syntax
GETDATE () instance
Example 1
Use the following SELECT statement:

Select getdate () AS CurrentDateTime result:

CurrentDateTime
16:25:46. 635

Note: the above time is accurate to milliseconds.

Example 2
The following SQL statement creates an "Orders" table with the date and time column (OrderDate:

Create table Orders
(
OrderId int not null primary key,
ProductName varchar (50) not null,
OrderDate datetime not null default getdate ()
)
Note that OrderDate specifies GETDATE () as the default value. The result is that when you insert a new row in the table, the current date and time are automatically inserted into the column.

Now, we want to insert a record in the "Orders" table:

Insert into Orders (ProductName) VALUES ('computer ')

GETDATE (): get the current date and time
DATEADD (datepart, number, date), calculate the date after the increase
For example, DATEADD (DAY, 3, date) calculates the date of 3 days after date.
DATEADD (MONTH,-8, date) calculates the date of the first 8 months of date
DATEDIFF (datepart, startdate, enddate): calculate the difference between two dates.
DATEPART (datepart, date): returns a specific part of a date.

Type conversion functions
CAST (expression AS data_type)
CONVERT (data_type, expression)

The null value processing function is the second parameter if the query result is null. If it is not null, it is the first parameter.
Select isnull (FName, 'Alias name') as name from T_Employee

CASE function usage

The CASE search function calculates a group of Boolean expressions to determine the result.
Both formats support the optional ELSE parameter.

Syntax
Simple CASE functions:

CASE input_expression
WHEN when_expression THEN result_expression
[... N]
    [
ELSE else_result_expression
END

CASE Search function:

CASE
WHEN Boolean_expression THEN result_expression
[... N]
    [
ELSE else_result_expression
END

Parameters
Input_expression

Is the expression calculated when the simple CASE format is used. Input_expression is any valid Microsoft® SQL Server™ expression.

WHEN when_expression

Simple expressions compared with input_expression in simple CASE format. When_expression is any valid SQL Server expression. The data type of Input_expression and when_expression must be the same or implicit conversion.

N

Placeholder, indicating that multiple WHEN when_expression THEN result_expression clauses or WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression

 

Single-value judgment, equivalent to switch case
CASE expression
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
ELSE defaultreturnvalue
END