SQL Time function, the need for friends can refer to the next. 1. Current system date, time select GETDATE ()
2. DateAdd returns a new datetime value based on adding a period of time to the specified date for example: Add 2 days to the date select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000
For example: Query current time last three days contents in descending order select * from table where time between DateAdd (Day,-3,getdate ()) and getdate () Order BY c_id Desc
3. DateDiff returns the number of date and time boundaries across two specified dates. Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17
4. DatePart returns an integer representing the specified date portion of the specified date. SELECT DATEPART (month, ' 2004-10-15 ')--returns 10 5. Datename returns a string representing the specified date portion of the specified date SELECT datename (Weekday, ' 2004-10-15 ')--return: Friday 6. Day (), month (), year ()--can be compared with DATEPART Select Current date =convert (varchar), GETDATE (), 120)
, Current time =convert (varchar (8), GETDATE (), Datename (DW, ' 2004-10-15 ')
Select how many weeks of the year =datename (week, ' 2004-10-15 ')
, today is the weekly =datename (weekday, ' 2004-10-15 ') function parameter/function GetDate () returns the current date and time of the system DateDiff (INTERVAL,DATE1,DATE2) in interval specified manner, Returns the difference between Date2 and date1 two dates date2-date1 DateAdd (interval,number,date) in interval-specified manner, plus the date after number DatePart (interval, Date) Returns the value of the integer value Datename (interval,date) that corresponds to the specified part of the interval, and the values of the string name parameter interval for the specified part of the interval in the date date, which is set as follows: value reduction Write (SQL Server) Access and ASP description year Yy yyyy 1753 ~ 9999 Quarter Qq Q Season 1 ~ 4
Month Mm M 1 ~ Day of the year Dy y the number of days of the year, the day of the first 1-366 day Dd D days, 1-31 Weekday Dw W one week of the day, the day of the week 1-7 Week Wk WW Week, the first of the year Week 0 ~ 51
Hour Hh H 0 ~ Minute Mi n minutes 0 ~ Second Ss s seconds 0 ~ 59
Millisecond MS-MS 0 ~ 999 access and ASP use Date () and now () to get the system datetime, where Datediff,dateadd,datepart is also used in Access and ASP, and the usage of these functions is similar
Example: 1.GetDate () for SQL Server:select GetDate ()
2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days
3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7)
DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25 or 25th DatePart (' y ', ' 2005-7-25 22:56:32 ') with a return value of 206 that is the No. 206 day of the Year DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') The return value is 2005, which is the 2005 SQL Server DATEPART () function returns part of SQL Server datetime field. The syntax for the SQL Server DATEPART () function is: DATEPART (portion, datetime) where datetime is the SQL Server datetime field and the part name is one of the following: Ms for Milliseco NDS Yy for year Qq for Quarter of the year Mm for Month Dy for the day of the year Dd for day of the Month Wk for Week Dw For the day of the Week Hh for Hour
Mi for Minute Ss for Second detailed description: Usually, you need to get the current date and calculate some other date, for example, your program may need to determine the first or last day of one months. Most of you probably know how to divide the date (year, month, day, etc.), and then just use the divided year, month, day, etc. in a few functions to calculate the date you need! In this article, I'll show you how to use the DateAdd and DATEDIFF functions to figure out some of the different dates you might want to use in your program. Before using the examples in this article, you must be aware of the following issues. Most probably not all examples of the results performed on different machines may not be the same, which is entirely determined by which day is the first day of one weeks of this setting. The first day (Datefirst) setting determines which day your system uses as the first day of the week. All of the following examples are created in Sunday as the first day of the week, that is, the first day is set to 7. If your first day is different, you may need to adjust these examples so that it matches the settings of the first day. You can check the first day setting by using the @ @DATEFIRST function.
To understand these examples, let's review the DateDiff and DATEADD functions. The DateDiff function calculates the total number of hours, days, weeks, months, years, and so on, between two dates. The DateAdd function calculates a date by adding or decreasing the time interval to obtain a new date. To learn more about the DateDiff and DATEADD functions and the time interval you can read Microsoft online Help.
Using the DateDiff and DATEADD functions to calculate dates is a little different from the way you would think to convert from the current date to the date you need. You have to think about it in terms of time interval. For example, how many intervals are there between the current date and the date you want to get, or how much time interval between today and a day (such as 1900-1-1), and so on. Understanding how to focus on time intervals can help you easily understand my different date calculation examples.
First day of one months
For the first example, I'll show you how to go to the last day of the month from the current date. Note: This example, and the other examples in this article, will only use the DateDiff and DATEADD functions to calculate the date we want. Each example is calculated by calculating the time interval, and then adding and subtraction to get the date you want to calculate.
This is the SQL script that calculates the first day of one months: SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)
Let's separate the statement to see how it works. The core function is getdate (), which most people know is a function that returns the current date and time. The next executed function, DateDiff (Mm,0,getdate ()), is the number of months between the date of the current date and "1900-01-01 00:00:00.000". Remember: Time and time variables, like milliseconds, are calculated starting with "1900-01-01 00:00:00.000". That's why you can specify the first time expression as "0" in the DateDiff function. The next function is DATEADD, increasing the number of months from the current date to "1900-01-01". By adding the predefined date "1900-01-01" and the number of months of the current date, we can get the first day of the month. In addition, the time portion of the calculated date will be "00:00:00.000".
The trick is to calculate the time interval of the current date to "1900-01-01" and then add it to "1900-01-01" to get a special date, a technique that can be used to calculate many different dates. The next example is to use this technique to produce different dates from the current date.
This week in Monday I am using the interval of weeks (wk) to calculate which day is the Monday of this week. SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0) The first day of the year now displays the first day of the year with a time interval of year (yy). SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0) first day of the quarter
If you want to calculate the first day of the quarter, this example tells you how to do it. SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0) midnight of the day
Once it was necessary to intercept the time portion by the GETDATE () function in order to return the time value, it would take into account whether the current date is in the middle of the night. If so, this example uses the DateDiff and DATEADD functions to get a midnight point in time.
SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0) deep DATEDIFF and DATEADD function calculations
You can understand that by using simple DateDiff and DateAdd function calculations, you can find many different dates that may be meaningful.
All the examples so far just calculate the time interval between the current time and "1900-01-01" and then add it to the "1900-01-01" interval to calculate the date. Assuming you modify the number of intervals, or use different intervals to invoke the DateAdd function, or subtract the time interval instead of the increment, you can find and vary the dates by these small adjustments.
Here are four examples using another DateAdd function to calculate the last day before and after two time intervals to replace the DATEADD function respectively. The last day of last month
This is an example of calculating the last day of last month. It is obtained by subtracting 3 milliseconds from this example on the last day of one months. One thing to keep in mind is that the time in SQL Server is exactly 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want. Select DATEADD (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0)) calculates the time portion of a date that contains a last minute of the day that SQL Server can record (" 23:59:59:997 ") time. Last day of last year
To get to the last day of last year, you need to subtract 3 milliseconds from the first sky this year. SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0)) Last day of the month
Now, in order to get the last day of the month, I need to revise a little bit to get the last day of last month's statement. The modification needs to be added 1 to the time interval returned by the DateDiff comparison between the current date and "1900-01-01". By adding 1 months, I calculated the first day of the next month and then subtracted 3 milliseconds, so that the last day of the month was calculated. This is the SQL script that calculates the last day of the month.
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0)) The last day of the year
You should now master this practice, which is to calculate the last day of the year script SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0)) The first Monday of this month
Well, now is the last example. Here I'm going to calculate the first Monday of this month. This is the computed script. Select DATEADD (wk, DATEDIFF (wk,0,
DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)
In this case, I used the "Monday of the Week"
SQL Time function
