SQL Server Learning Notes <> date and time data processing (cast conversion format, date interception, date addition and subtraction) and case expressions

Processing of date and time data.

(1) String date

' 20080301 ', which is a string date, but must be guaranteed as a four-bit year, two-bit month, and two-bit date. For example, the Query order table date is greater than ' 20080301 '. It can be written like this:

1  SELECT * from Sales.orders2  where orderdate> ' 20080301 '

Results:

(2) cast for conversion. For example, you can say ' 20080301 ' into a time type. And the result is the same.

1  SELECT * from Sales.orders2  where Orderdate>cast (' 20080301 ' as datetime)

(3) DatePart, the interception date of a paragraph, year represents the interception of years, month represents the interception of the month, day represents the date of interception. Wait a minute.....

For example, intercept a portion of the current time.

1  Select DATEPART (year,getdate ()) as n ' Year ', 2         datepart (Month,getdate ()) as N ' Month ', 3         datepart (day,getdate ( ) as N ' Day ', 4         datepart (Hour,getdate ()) as n ' time ', 5         datepart (minute,getdate ()) as n ' Min ', 6         datepart (second, GETDATE ()) as N ' second '

Results

(4) Special date interception section, for example:

1   Select DATEPART (dayofyear,getdate ()) as n ' the day ordinal of the Year ', 2         datepart (Weekday,getdate ()) as n ' The day of the Week ', 3         DatePart (Week,getdate ()) as N ' The Week of the Year '

Results

(5) Date of addition and subtraction. DATEADD (Growth unit (year, month, day), Step (growth), base), DATEDIFF (unit, step, base).

For example: What is the current date plus 20 days? How long has my beloved motherland been founded? How old is Ben Dick this year?

1  Select DATEADD (Day,20,getdate ()) as n ' 20 days after what day ', 2         datediff (year, ' 19491001 ', GETDATE ()) as n ' The motherland was founded so many years ago, 3         DateDiff (year, ' 19911002 ', GETDATE ()) as N ' dick wire how Big

(6) Cast and convert, both can speak string or other forms of conversion to the specified type. The difference is: Convert conversion can be specified in the style of conversion, cast direct conversion.

For example:

1  Select CONVERT (Nvarchar,getdate (), as n ' converted form ', 2 left        (CONVERT (Nvarchar,getdate (),), 6) as n ' taken out of the month '

Results

Case expression

(1) Simple expression, for example, to query the employee table inside the area (region) for WA's "Washington Land District" instead of the expression.

1  Select firstname,lastname,2 case  region3 if  ' WA ' Then ' Washington Land District ' 4  Else ' other ' 5  End6  from Hr.employees

(2) Search expression, here we first query the employee table (Hr.employees inside the information.

1  Select Firstname,lastname,region2 from  hr.employees

If the region is null, we want to use "Unknown region" to express, how to write it? In this case, you need to use the search expression, you can write:

1  Select firstname,lastname,2 Case if  region = ' WA ' Then ' Washington Land District ' 3 when       zone is null then ' unknown area ' 4  els E ' other regions ' 5  End6 from  hr.employees

Results:

SQL Server Learning Notes <> date and time data processing (cast conversion format, date interception, date addition and subtraction) and case expressions