One, Time function
When you use stored procedures, SQL functions, you encounter some processing of time. such as the acquisition of time and the addition and subtraction. In this case, the SQL comes with the time function. Below I list these functions, easy to remember later, use.
--getdate Get current time Select getdate() --DateAdd Original time add: 2013-02-17 13:20:16 this time plus 12 months Select DateAdd(MONTH, A,'2013-02-17 13:20:16')--return: 2014-02-17 13:20:16.000 (parameter month can be changed to Day,year date plus corresponding value) --DateDiff Two time difference (back-front = return value) Select DateDiff( Day,'2013-02-01','2013-02-18')--return: 17 (parameter day can be changed to Month,year date plus corresponding value) --datepart gets a partial integer of the date Select DATEPART(Month,'2013-2-17')--returns 2 (parameter month can be changed to Day,year date plus corresponding value) --Datename Gets the string for the specified part Select Datename(Weekday,'2013-2-17')--return to Sunday (parameter weekday can be changed to Day,year date plus corresponding value) --Day (), Month (), year () gets the string for the specified part Select Day('2013-2-15')--back to
Second, time format conversion
Select CONVERT(varchar,getdate(), -)--back to 2013-02-17 13:37:54 Select Replace(Replace(Replace(CONVERT(varchar,getdate(), -),'-',"'),' ',"'),':',"')--back to 20130217133828 Select CONVERT(varchar( A) ,getdate(),111)--back to 2013/02/17 Select CONVERT(varchar( A) ,getdate(), the)--back to 20130217 Select CONVERT(varchar( A) ,getdate(),102)--back to 2013.02.17 Select CONVERT(varchar( A) ,getdate(),101)--back to 02/17/2013 Select CONVERT(varchar( A) ,getdate(),103)--back to 17/02/2013 Select CONVERT(varchar( A) ,getdate(),104)--back to 17.02.2013 Select CONVERT(varchar( A) ,getdate(), the)--back to 17-02-2013 Select CONVERT(varchar( A) ,getdate(),106)--return to Select CONVERT(varchar( A) ,getdate(),107)--back to Geneva, Select CONVERT(varchar( A) ,getdate(),108)--back to 13:42:50 Select CONVERT(varchar( A) ,getdate(),109)--back to Geneva Select CONVERT(varchar( A) ,getdate(), the)--back to 02-17-2013 Select CONVERT(varchar( A) ,getdate(),113)--back to 1 Select CONVERT(varchar( A) ,getdate(), the)--back to 13:42:24:743
Three, time format related tables
Function |
Parameters/Functions |
GetDate () |
Returns the current date and time of the system |
DateDiff (INTERVAL,DATE1,DATE2) |
Returns the difference between Date2 and date1 two dates, as specified by interval date2-date1 |
DATEADD (Interval,number,date) |
The date specified by interval, plus number |
DatePart (Interval,date) |
Returns the integer value that corresponds to the specified part of the date interval |
Datename (Interval,date) |
Returns the string name corresponding to the specified part of the date, interval
|
Value |
Abbreviation (SQL Server) |
Access and ASP |
Description |
Year |
Yy |
yyyy |
Year 1753 ~ 9999 |
Quarter |
Qq |
Q |
Season 1 ~ 4 |
Month |
Mm |
M |
Month 1 ~ 12 |
Day of the Year |
Dy |
Y |
The number of days in a year, the day of the year 1-366 |
Day |
Dd |
D |
Day, 1-31 |
Weekday |
Dw |
W |
The number of days in a week, the day of the week 1-7 |
Week |
Wk |
Ww |
Week, week of the year 0 ~ 51 |
Hour |
Hh |
H |
Time 0 ~ 23 |
Minute |
Mi |
N |
Minutes 0 ~ 59 |
Second |
Ss |
S |
seconds 0 ~ 59 |
Millisecond |
Ms |
- |
Milliseconds 0 ~ 999 |
Iv. examples
1, the common date method (below getdate () = ' 2006-11-08 13:37:56.233 ')
(1) Datename (datepart, date)
Returns a String that represents the specified date portion of the specified date. DatePart See the list below.
SELECT Datename (Day,getdate ()) – returns 8
(2) DATEPART (DATEPART, date)
Returns an integer that represents the specified date portion of the specified date.
SELECT DATEPART (Year,getdate ()) – returns 2006
(3) DATEADD (datepart, number, date)
Returns a new datetime value that is added to the specified date after a time interval.
SELECT DATEADD (Week,1,getdate ())--date plus one week after the current date
(4) DATEDIFF (DatePart, StartDate, EndDate)
Returns the number of date boundaries and time boundaries across two specified dates.
SELECT DATEDIFF (month, ' 2006-10-11 ', ' 2006-11-01 ')--return 1
(5) Day (date)
Returns an integer that represents the day datepart portion of the specified date.
SELECT Day (GetDate ()) – returns 8
(6) GETDATE ()
Returns the current system date and time as a datetime value in the SQL Server 2005 standard internal format.
SELECT GetDate ()--Return 2006-11-08 13:37:56.233
(7) MONTH (date)
Returns an integer that represents the month portion of a specified date.
SELECT MONTH (GETDATE ())--Return 11
(8) Year (date)
Returns an integer that represents the year part of the specified date.
SELECT year (GETDATE ())--Return 2006
2. Take a specific date
(1) The day of the week to get the current date
SELECT Datename (Weekday,getdate ())--wednesday
(2) Calculate which day is the Monday of this week
SELECT DATEADD (Week, DATEDIFF (week, ' 1900-01-01 ', GETDATE ()), ' 1900-01-01 ')--return 2006-11-06 00:00:00.000
Or
SELECT DATEADD (Week, DATEDIFF (Week,0,getdate ()), 0)
(3) The first day of the current quarter
SELECT DATEADD (Quarter, DATEDIFF (Quarter,0,getdate ()), 0)-Return 2006-10-01 00:00:00.000
(4) How to get the number of days in a month
SELECT Day (DateAdd (Ms,-3,dateadd (mm, DATEDIFF (m,0, ' 2006-02-03 ') +1,0))-Return 28
(5) How many days of a quarter
Declare @m tinyint, @time smalldatetime
Select @m=month (getdate ())
Select @m=case when @m between 1 and 3 then 1
When @m between 4 and 6 then 4
When @m between 7 and 9 then 7
Else Ten End
Select @time =datename (year,getdate ()) + '-' +convert (varchar), @m) + '-01 '
Select DateDiff (Day, @time, DateAdd (mm,3, @time))-return 92
(6) Date of acquisition (YYYY-MM-DD)
SELECT CONVERT (VARCHAR), GETDATE (), 120) – Return 2006-11-08
3. Other
(1)--The following example specifies a date as a number. The database engine interprets 0 as January 1, 1900.
SELECT MONTH (0), day (0), year (0) – return 1 1 1900
--The following two sentences are equivalent
SELECT Datename (weekday,0)
SELECT datename (WEEKDAY, ' 1900-01-01 ')
(2) SET Datefirst {number | @number_var}
Set the first day of the week to a number from 1 to 7.
SET Datefirst 1--Indicates that the first day of the week is "Monday"
SELECT Datename (Weekday,getdate ())--wednesday
SELECT DATEPART (Weekday,getdate ())--Return 3
--View current settings
SELECT @ @DATEFIRST
(3) SET DateFormat {format | @format_var}
Sets the order of the date parts (month/day/year) that are used to enter datetime or smalldatetime data.
... Valid parameters include MDY, dmy, Ymd, YDM, MyD, and Dym.
... This setting is used only in the interpretation when converting a string to a date value. It does not affect the display of date values.
... Set DateFormat settings are set at execution or runtime, not at parse time.
... Set DateFormat overrides the implicit date format setting for set LANGUAGE.
Here is an example:
--Set date format to year, day, month.
SET DateFormat YDM;
GO
DECLARE @datevar DATETIME;
SET @datevar = ' 1998/31/12 ';
SELECT @datevar as Datevar;
GO
--Set date format to year, month, day.
SET DateFormat Ymd;
GO
DECLARE @datevar DATETIME;
SET @datevar = ' 1998/12/31 ';
SELECT @datevar as Datevar;
GO
(4) List of date parts
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 |
Data type |
Range |
Accuracy |
Datetime |
January 1, 1753 to December 31, 9999 |
3.33 ms |
smalldatetime |
January 1, 1900 to June 6, 2079 |
SQL Server Date Conversion