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