TSQL Basics (iv)-date processing

DateTime is the most commonly used date type in SQL.

Storage size: 8 bytes;

Date range: 1753-01-01 to 9999-12-31;

Accuracy: 3.33 ms;

Common Date function getdate () Gets the current date of the system

Select GETDATE ()

DatePart (Part,dtvalue) Gets the part of the date that part is going to get, Dtvalue is the date

The part parameters are:

Date part abbreviation
Year yy, yyyy
Quarter QQ, Q
month mm, M
DayOfYear Dy, y
Day DD, D
Week wk, WW
Weekday DW
Hour hh
Minute MI, n
Second SS, S
Millisecond MS

         Select GETDATE () as ' system current date ', DATEPART (year, GETDATE ()) as ' Years ', DATEPART (month,get                DATE ()) as ' month ', DATEPART (Day,getdate ()) as ' Day ', DATEPART (Hour,getdate ()) as ' hour ', DATEPART (Minute,getdate ()) as ' Sub ', DATEPART (Second,getdate ()) as ' s ', DATEPART (millisec Ond,getdate ()) as ' millisecond ', DATEPART (Quarter,getdate ()) as ' quarter ', DATEPART (Dayofyear,getdate ()) As ' from the beginning of the year to the present days ', DATEPART (Weekday,getdate ()) as ' Today is the day of the week, starting from Sunday ', DATEPART (Week,getdate () As ' The week of the Year '--part abbreviated select GETDATE () as ' system current date ', DATEPART (YYYY, GETDATE ()) a S ' Year ', DATEPART (Mm,getdate ()) as ' month ', DATEPART (Dd,getdate ()) as ' Day ', DATEPART                (Hh,getdate ()) as ' Hour ', DATEPART (Mi,getdate ()) as ' min ', DATEPART (Ss,getdate ()) as ' s ', DATEPART (Ms,getDATE ()) as ' milliseconds ', DATEPART (Qq,getdate ()) as ' quarter ', DATEPART (Dy,getdate ()) as ' from the beginning to the present days ', DATEPART (Dw,getdate ()) as ' Today is the day of the week, starting from Sunday ', DATEPART (Wk,getdate ()) as ' the first few weeks of this year '

Datename returns a string representing the specified date part of a specified date

  Select Datename (Weekday, ' 2014-12-8 ')     select Datename (YY, ' 2014-12-8 ')   

Year (Dtvalue), month (Dtvalue), Day (Dtvalue) respectively gets the date in: the part of the years, the months, the days

Select year (' 2014-01-12 '),        Month (' 2014-01-12 '), Day        (' 2014-01-12 ')                               

DATEADD (Part,num,dtvalue) adds minus for a given date, Num is positive, plus, minus

Select    ' 19901221 ' as '  given date ',        dateadd (year,2, ' 19901221 ') as ' plus 2 years ',         dateadd (year,-2, ' 19901221 ') As ' minus 2 years ',        dateadd (month,12, ' 19901221 ') as ' plus 12 months ',        dateadd (month,-12, ' 19901221 ') as ' minus 12 months ',         DateAdd (day,31, ' 19901221 ') as ' plus 31 days ',        

DateDiff (part,dtvalue1.dtvalue2) calculates the interval between 2 dates

Select  DateDiff (year, ' 1988-12-21 ', ' 1990-12-21 '),--large time at the back interval for positive                DateDiff (years, ' 1990-12-21 ', ' 1988-12-21 ' )--large time at the front face interval is negative    

Date format convert (Datatype,value[,style]) function to convert an expression from one data type to another, and you can convert a date to a general function of a new data type

Style ID	Style Format
100 or 0	Mon dd yyyy hh:miam (or PM)
101	Mm/dd/yy
102	Yy.mm.dd
103	Dd/mm/yy
104	Dd.mm.yy
105	Dd-mm-yy
106	DD Mon yy
107	Mon DD, yy
108	Hh:mm:ss
109 or 9	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	Mm-dd-yy
111	Yy/mm/dd
112	Yymmdd
113 or 13	DD Mon yyyy hh:mm:ss:mmm (24h)
114	Hh:mi:ss:mmm (24h)
120 or 20	Yyyy-mm-dd Hh:mi:ss (24h)
121 or 21	Yyyy-mm-dd hh:mi:ss.mmm (24h)
126	Yyyy-mm-ddthh:mm:ss.mmm (no spaces)
130	DD Mon yyyy hh:mi:ss:mmmAM
131	Dd/mm/yy Hh:mi:ss:mmmAM

  Select GETDATE (),                    convert (nvarchar), GETDATE (), as ' style=120 ',                    convert (nvarchar, GETDATE (), 101) As ' style=101 ',                    CONVERT (nvarchar (), GETDATE (), as ' style=112 ')

By the way, learn about the cast (expression as datatype[(length)]) function

The cast () and CONVERT () functions are similar in that you can convert an expression from one data type to another, but you cannot set the time format like the CONVERT () function

Cast () is an ANSI standard SQL, and the cast () conversion is preferred unless formatting is required.

Select  cast (' + '  as decimal (18, 2)) +11,--string converted to decimal convert                 (decimal (2), ' + ') +11,-- Convert String to Decimal                 

Date function applies the date of the first day of the year

Select DATEADD (yy,2, ' 2011 ')

When the DateAdd year is added, the month days will be the initial values. You can use this feature to find the date of the first day.

1. Select a date as "median" to figure out how many years the difference between this year and this "middle value" is.

2. Then use "median" plus the year in the first step to find the difference.

Select DATEDIFF (yy, ' s ', GETDATE ())--1. Find out the median 2011 and this year's difference between the year is how many select DATEADD (yy,   DATEDIFF (yy, ' s ', GETDATE ()) ,   ' 2011 ')--2. Then use "median" plus the year in the first step to find the difference.

"Median" generally write 0, write 0 o'clock this date is the default value ' 1900-01-01 '

Select DATEDIFF (yy,0,getdate ()) Select DATEADD (yy,   DATEDIFF (Yy,0,getdate ()),  0) Select DATEDIFF (yy, ' 1900-01-01 ', GETDATE ()) Select DATEADD (yy,   DATEDIFF (yy, ' 1900-01-01 ', GETDATE ()),   ' 1900-01-01 ')

Then the last year or the first day of next year, you just need to add and subtract the year after the difference.

Select DATEADD (yy,   DATEDIFF (Yy,0,getdate ())-1,  0)--one day before the beginning of the year select DATEADD (yy,   DATEDIFF (Yy,0,getdate () ) +1,  0)--one day of the following year select DATEADD (yy,   DATEDIFF (yy,0, '),  0)--Start day 2008

Then the date of the nth day of this year only need to add (N-1) days to

Select DATEADD (yy,   DATEDIFF (Yy,0,getdate ()),  0) + (101-1)--date of the 101th day of the year

Then the date of the first day of this month and the date of the Nth Day, the first day of the week and the date of the nth day only need to change part parts

   --First day of the month  select DATEADD (mm,   DATEDIFF (Mm,0,getdate ()), 0)     --13th day of the month  select DATEADD (mm,   DATEDIFF (Mm,0,getdate ()), 0) + (13-1)   --the first day of the week  select DATEADD (WK,   DATEDIFF (Wk,0,getdate ()), 0)     -- The third day of the week  select DATEADD (WK,   DATEDIFF (Wk,0,getdate ()), 0) + (3-1)

Find the date of the last day of the year

Find the date of the first day of the next year and subtract 3 milliseconds.

    Select DATEADD (yy,   DATEDIFF (Yy,0,getdate ()) +1,   0)--the first day of the next year    select   DATEADD (yy,   DATEDIFF (Yy,0,getdate ()) +1,   0))  --Get the start of the next year and subtract 3 milliseconds.

Why minus 3 milliseconds? What else can I do?

Because the time precision of a datetime type is 3.33 milliseconds, the precision is approximated to 0.000,0.003,0.007 seconds if it is exceeded

    Select DATEADD (ms,-1, ' 2015-01-01 00:00:00.000 ') as ' minus 1 milliseconds ', DATEADD (ms,-2, ' 2015-01-01 00:00:00.000 ') as ' minus 2  Milliseconds ', DATEADD (ms,-3, ' 2015-01-01 00:00:00.000 ') as ' minus 3 milliseconds ', DATEADD (ms,-4, ' 2015-01-01 00:00:00.000 ') as ' Minus 4 milliseconds ', DATEADD (ms,-5, ' 2015-01-01 00:00:00.000 ') as ' minus 5 milliseconds ' select DATEADD (ms,-6, ' 2015-01-01 00:00:00.000 ') as ' minus 6 milliseconds ', DATEADD (ms,-7, ' 2015-01-01 00:00:00.000 ') as ' minus 7 milliseconds ', DATEADD (ms,-8, ' 2015-01 -01 00:00:00.000 ') as ' minus 8 milliseconds ', DATEADD (ms,-9, ' 2015-01-01 00:00:00.000 ') as ' minus 9 milliseconds ', DATE  ADD (ms,-10, ' 2015-01-01 00:00:00.000 ') as ' minus 10 milliseconds ' Select DATEADD (ms,-11, ' 2015-01-01 00:00:00.000 ') as ' Minus 11 milliseconds ', DATEADD (ms,-12, ' 2015-01-01 00:00:00.000 ') as ' minus 12 milliseconds ', DATEADD (ms,-13, ' 2015-01-01 00:00:00. As ' minus 13 milliseconds ', DATEADD (ms,-14, ' 2015-01-01 00:00:00.000 ') as ' minus 14 milliseconds ', DATEADD (ms,-15, ' 2015-01-01 0 0:00:00.000 ') as ' minus 15 milliseconds ' 

Then the last year or the first day of next year, you just need to add and subtract the year after the difference.

Select   DateAdd (Ms,-3,dateadd (yy,   DATEDIFF (Yy,0,getdate ()),   0))  --Last day of the year select   DateAdd (MS, -3,dateadd (yy,   DATEDIFF (Yy,0,getdate ()) +2,   0))--Last day of the next year select   DATEADD (yy,   DATEDIFF (yy,0, ' +1, 0))   --Last day of 2008

Then ask for the date of the last day of the month, and ask for the last day of the week only the part can be changed.

  Select   DateAdd (Ms,-3,dateadd (mm,   DATEDIFF (Mm,0,getdate ()) +1,  0))    --Last day of the month       select   DateAdd (Ms,-3,dateadd (WK,   DATEDIFF (Wk,0,getdate ()) +1,   0))   --the last day of the week  

For the first Monday of this month

First look at how to get this week's Monday

1. How many weeks is the difference from ' 1900-01-01 ' Today,
2. ' 1900-01-01 ' plus the difference of the number of weeks (plus the number of days = Weeks *7). and ' 1900-01-01 ' happens to be Monday, so the addition of this day is Monday.

--the first day of the week select DATEADD (WK,   DATEDIFF (Wk,0,getdate ()), 0), Datename (Weekday,dateadd (WK, DATEDIFF (wk,0),   GETDATE ()), 0))

Then the third day of the week, that is, the week 3, the value needs to change ' 1900-01-01 ' to ' 1900-01-03 ' can

Select DATEADD (WK,   DATEDIFF (WK, ' 1900-01-03 ', GETDATE ()), ' 1900-01-03 '), Datename (Weekday,dateadd (WK,   DATEDIFF (WK, ' 1900-01-03 ', GETDATE ()), ' 1900-01-03 ')

Then the first Monday of the month

1. Find out how many weeks away from ' 1900-01-01 ' a day in the first one weeks of the month
2. ' 1900-01-01 ' plus the difference of the number of weeks (plus the number of days = Weeks *7). and ' 1900-01-01 ' happens to be Monday, so the addition of this day is Monday.

This one day needs to be concealed 1 conditions---One day must be in the same one weeks as Monday.

1th No.       2nd No.      3rd No. 4th No. 5th No. 6th No.       7th  Sunday    Week 1    weeks 2   weeks 3    weeks 4     weeks 5 weeks      6  - -both and Monday in the same one weeks Week 1     weeks 2    weeks 3   weeks 4    weeks 5     weeks 6      Sunday-This place 7th eliminated has Monday but 7th for 2nd week Week 2     weeks 3    Week 4   weeks 5    weeks 6     Sunday     weeks 1  --this place 1,2,3,4,5 eliminated not and the first Monday of the month in the same one weeks Week 3 weeks     4    weeks 5   Week 6    Sunday    Week 1      Week 2  --1,2,3,4 eliminated not and the month first Monday in the same one weeks Week 4     weeks 5    weeks 6   Sunday   weeks 1     Week 2      Week 3  -----------------  --eliminated the not and the month first Monday in the same one weeks 6     Sunday   weeks 1   weeks 2    weeks 3     weeks 4      weeks 5   ---1 eliminated Not with the first Monday of the month in the same one weeks

It can be seen that the number 6th per month is always in the same one weeks as Monday.

Note that the first day of every week in SQL Server is Sunday

Select Datename (Week, ' 1900-01-01 '), Datename (WEEKDAY, ' 1900-01-01 '), select Datename (Week, ' 1900-01-01 '), Datename ( WEEKDAY, ' 1900-01-06 ') Select Datename (Week, ' 1900-01-07 '), Datename (WEEKDAY, ' 1900-01-07 ')

The first Monday of the month

--Get this month's number 6th select DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ())--Get this month's 6th distance from ' 1900-01-01 ' How many weeks is the difference between select DATEDIFF (wk, 0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ())--' 1900-01-01 ' plus the number of days of the week (plus day = day of the week *7). and ' 1900-01-01 ' happens to be Monday, so add this day also for Monday select  DATEADD (Wk,datediff (Wk,0,dateadd Dd,6-datepart (Day,getdate ()), GETDATE ())), 0),            

1th Wednesday of the month

Select  DATEADD (Wk,datediff (wk, ' 1900-01-03 ', DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ())), ' 1900-01-03 '),                    datename (Weekday,dateadd (Wk,datediff (wk, ' 1900-01-03 ', DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ())), ' 1900-01-03 '))

So the 2nd Monday of the month

Select  DATEADD (Wk,datediff (Wk,0,dateadd (Dd,13-datepart (Day,getdate ()), GETDATE ()), 0),            datename (WEEKDAY , DATEADD (Wk,datediff (Wk,0,dateadd (Dd,13-datepart (Day,getdate ()), GETDATE ()), 0))

Other

Select  DateAdd (Day,2-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (),)  --Get this week Monday Select DATEADD (Day,9-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), ())   --Get Next Monday select DATEADD (day,1-day (GETDATE ()), CONVERT (Varchar,getdate (), ())  --Get the 1th select DateAdd this month (Month,1,dateadd (Day,1-day ()), Convert (Varchar,getdate (), 112))--Get next month a select DATEADD (Month,-1,dateadd (Day,1-day (getdate ()), CONVERT (varchar, GETDATE (), ()))  --Get a last month

TSQL Basics (iv)-date processing