SQL Time function

--Date conversion parameters

Select CONVERT (varchar, GETDATE (), 120) 2004-09-12 11:06:08

Select replace (replace (varchar, GETDATE (), 120), '-', '), ', ', ', ', ') 20040912110608

Select CONVERT (varchar (), GETDATE (), 111) 2004/09/12

Select CONVERT (varchar (), GETDATE (), 112) 20040912

Select CONVERT (varchar (), GETDATE (), 102) 2004.09.12

Other I do not use the date format conversion method:

Select CONVERT (varchar (), GETDATE (), 101) 09/12/2004

Select CONVERT (varchar (), GETDATE (), 103) 12/09/2004

Select CONVERT (varchar (), GETDATE (), 104) 12.09.2004

Select CONVERT (varchar (), GETDATE (), 105) 12-09-2004

Select CONVERT (varchar (), GETDATE (), 106) 12 09 2004

Select CONVERT (varchar (), GETDATE (), 107) 09 12, 2004

Select CONVERT (varchar (), GETDATE (), 108) 11:06:08

Select CONVERT (varchar (), GETDATE (), 109) 09 12 2004 1

Select CONVERT (varchar (), GETDATE (), 110) 09-12-2004

Select CONVERT (varchar (), GETDATE (), 113) 12 09 2004 1

Select CONVERT (varchar (), GETDATE (), 114) 11:06:08.177

1. Current system date, time

Select GETDATE ()

2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date

For example: Add 2 days to date

Select DATEADD (day,2, ' 2004-10-15 ')--back: 2004-10-17 00:00:00.000

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 part of the specified date.

SELECT DATEPART (month, ' 2004-10-15 ')--return 10

5. Datename returns a string representing the specified date part 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 (), 114)

Select Datename (DW, ' 2004-10-15 ')

Select how many weeks of the year =datename (week, ' 2004-10-15 ')

, today is the week =datename (weekday, ' 2004-10-15 ')

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) in the manner specified by interval, plus the date after number

DatePart (interval,date) returns the integer value of the specified part of the date, interval

Datename (Interval,date) returns the string name of the specified part of the date, interval

The set values for the parameter interval are as follows:

value abbreviation (SQL Server) Access and ASP description

Year Yy yyyy 1753 ~ 9999

Quarter Qq Q Season 1 ~ 4

Month Mm M Month 1 ~ 12

Day of the year Dy y, number of days of the year, 1-366

Day Dd D, 1-31

Weekday Dw W number of days in a week, day of the week 1-7

Week Wk WW Week, the first weeks of the year 0 ~ 51

Hour Hh H 0 ~ 23

Minute Mi N min 0 ~ 59

Second Ss S SEC 0 ~ 59

Millisecond ms-milliseconds 0 ~ 999

Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also used to

In Access and ASP, the use 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 i.e. 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 ') returns a value of 206 that is the No. 206 Day of the Year

DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 i.e. 2005

The 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 a SQL Server datetime field and part of the name is one of the following: Ms for milliseconds

Yy for year

Qq for quarter of the year

Mm for Month

Dy for the "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 judge the first day of one months or the most

After the day. Most of you probably know how to split dates (year, month, day, etc.), and then just split the year and month

, day, etc. put in several functions to calculate the date you need! In this article, I will show you how to use DateAdd and

DateDiff function to calculate 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 may not be the result of all the examples executed on different machines

May not be the same, this is entirely determined by which day is the first day of the one week of this setting. The first day (Datefirst) setting determines your

Which day the system uses as the first day of the week. All of the following examples are established in Sunday as the first day of the week, the first

The day is set to 7. If your first day setting is not the same, you may need to adjust these examples so that it matches the different first-day settings.

You can check the first day setting by using the @ @DATEFIRST function.

To understand these examples, let's review the DateDiff and DATEADD functions first. The DateDiff function calculates the hour between two dates,

The total number of days, weeks, months, years, and other time intervals. The DateAdd function calculates a date by adding and reducing time intervals to obtain a new date.

Learn more about DateDiff and DATEADD functions and time intervals to read Microsoft online Help.

Using the DateDiff and DATEADD functions to calculate dates is a little different from the way you think about converting from the current date to the date you need.

You have to consider this in terms of time intervals. For example, from the current date to the date you want to get between the time interval, or, from

How many time intervals are there between today and 1900-1-1, and so on. Understanding how to focus on time intervals can help you relax.

To solve my different date calculation examples.

The first day of one months

For the first example, I will show you how to go from the current date to the last day of the month. Please note: This example and the others in this article

Examples will only use the DateDiff and DATEADD functions to calculate the date we want. Each example will be calculated but before the time between

And then add and subtract to get the date you want to calculate.

This is the SQL script that calculates the first day of the one month:

SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

Let's take this statement apart to see how it works. The most central function is getdate (), which most people know is returned when

Before the date and time of the function. The next executed function DateDiff (Mm,0,getdate ()) is the calculation of the current date and "1900-01-01

00:00:00.000 "The number of months between this date. Remember: times and time variables are the same as milliseconds from "1900-01-01 00:00:00.000"

Start the calculation. That's why you can specify the first time expression in the DateDiff function to be "0". The next function is DateAdd

, increasing the number of months from the current date to "1900-01-01". By adding a predefined date of "1900-01-01" and the number of months of the current date, we can

To 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 between the current date and the "1900-01-01" and add it to "1900-01-01" to get special

Unlike dates, this technique can be used to calculate many different dates. The next example also uses this technique to produce a different from the current date

Date.

Monday of the Week

Here I use the WK time interval to calculate which day is this week's Monday.

SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

The first day of the year

The first day of the year is now displayed with the year (yy) interval.

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 what to do.

SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

The middle of the day

The GETDATE () function was used to truncate the time section to return the time value, taking into account whether the current date is in the middle of the night. If this

Sample, this example uses the DateDiff and DATEADD functions to get a midnight point.

SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

Deep DateDiff and DateAdd function calculation

You can see that by using simple DateDiff and DateAdd function calculations, you can find many different dates that may be meaningful.

All the examples so far are just calculating the current time and the number of time intervals between "1900-01-01" and then adding it to "1900

-01-01 "time interval to calculate the date. Suppose you modify the number of time intervals, or use different time intervals to invoke

DateAdd function, or subtract the time interval instead of increasing, then you can find and how many different dates through these small adjustments.

Here are four examples that use another DateAdd function to calculate the last day to replace the DATEADD function two intervals before and after each other.

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 the example on the last day of one months. A little

Keep in mind that the time in SQL Server is accurate to 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))

The time portion of the calculated date contains the time that a SQL Server can record the last moment of the day ("23:59:59:997").

Last day of last year

To connect the above example, to get the last day of last year, you need to subtract 3 milliseconds from the first sky of the year.

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))

The 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. Modifications need to be used

DateDiff compares the current date with the time interval returned by "1900-01-01" plus 1. By adding 1 months, I calculated the first day of the next month,

Then subtract 3 milliseconds, so that the last day of the month is 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 want 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)