SQL Server Date Function summary

Get one months of days: first to the date of the last day of the one month, by using the SQL Server Date function day () to get the "days" part of the date

Number of days to get February 2008:
Select Day (CAST (' 2008-03-01 ' as DateTime)-1)

Get the number of days this month:

Returns an integer that indicates which day of the month the specified date is.

Select Day (DateAdd (Month,1,getdate ()) – Day (GetDate ()))

Get the number of days last month:
Select Day (getdate ()-day (GETDATE ()))

Note: The SQL Server Date function day (@date) is equivalent to the DATE function datepart (day, @date)

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
Example: Add 2 days to the date
Select DATEADD (day,2, ' 2004-10-15 ')--return: 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 portion 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

Description of the value abbreviation (SQL Server) (Access and ASP)
Year Yy yyyy 1753 ~ 9999
Quarter Qq Q Season 1 ~ 4
Month Mm M 1 ~ 12
Day of the year Dy y the number of days of the year, the day of the first 1-366
Day Dd D, 1-31
Weekday Dw W Day of the week, day of the week 1-7
Week Wk WW Week, week of the year 0 ~ 51
Hour Hh H 0 ~ 23
Minute Mi N min 0 ~ 59
Second Ss s seconds 0 ~ 59
Millisecond MS-MS 0 ~ 999

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 ') 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 that is 2005

Use the CONVERT function in SQL Server to handle date strings:

For example, a beginner might write in a format like "2008-2-27":

[HTML]View Plaincopyprint?

1. Select CONVERT (varchar (4), Year (GETDATE ())) + '-' + CONVERT (varchar (2), Month (GETDATE ())) + '-' + CONVERT (varchar (2), Day ( GETDATE ()))

But what about the "2008-02-27" format that customers request?! I think it's going to be crazy, and you might write it like this:
[HTML]View Plaincopyprint?

2. Select
4. CONVERT (varchar (4), Year (GETDATE ())) + '-' +
6. Case month (GETDATE ())
8. When 1 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
10. When 2 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
12. When 3 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
14. When 4 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
16. When 5 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
18. When 6 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
20. When 7 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
22. When 8 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
24. When 9 Then ' 0 ' + CONVERT (varchar (2), Month (GETDATE ()))
26. ELSE CONVERT (varchar (2), Month (GETDATE ())) END +
28. '-' +
30. Case Day (getdate ())
32. When 1 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
34. When 2 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
36. When 3 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
38. When 4 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
40. When 5 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
42. When 6 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
44. When 7 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
46. When 8 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
48. When 9 Then ' 0 ' + CONVERT (varchar (2), Day (GETDATE ()))
50. ELSE CONVERT (varchar (2), Day (GETDATE ())) END

Don't laugh! I've really seen someone write this! hehe ~

In fact, SQL Server has already had a CONVERT function that can help us do this! If you want to output the 2008-02-27 date format string, you can write this:

[HTML]View Plaincopyprint?

1. SELECT CONVERT (char), GETDATE (), 120)

Below are some of the languages I use:

• Output format: 2008-02-27 00:25:13
  
  SELECT CONVERT (char), GETDATE (), 120)
• Output format: 2008-02-27
  
  SELECT CONVERT (char), GETDATE (), 12)
• Output format: 2008.02.27
  
  SELECT CONVERT (char), GETDATE (), 102)
• Output format: 08.02.27
  
  SELECT CONVERT (char (8), GETDATE (), 2)
• Output format: 2008/02/27
  
  SELECT CONVERT (char), GETDATE (), 111)
• Output format: 08/02/27
  
  SELECT CONVERT (char (8), GETDATE (), 11)
• Output format: 20080227
  
  SELECT CONVERT (char (8), GETDATE (), 112)
• Output format: 080227
  
  SELECT CONVERT (char (6), GETDATE (), 12)

For a complete list and to explain the details of CAST and CONVERT to the Transact-SQL Reference of MSDN.

When the database is taken out, the conversion is good.
Select GETDATE ()
2006-05-12 11:06:08.177

Select Convert (varchar), GETDATE (), 120)
2006-05-12

Select CONVERT (varchar, GETDATE (), 120)
2006-05-12 11:06:08

Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ', '), ': ', ')
20060512110608

Select CONVERT (varchar), GETDATE (), 111)
2006/05/12

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

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

Several other infrequently used date format conversion methods:

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

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

Select CONVERT (varchar), GETDATE (), 105)
12-05-2006

Select CONVERT (varchar), GETDATE (), 106)
12 05 2006

Select CONVERT (varchar), GETDATE (), 107)
05 12, 2006

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

Select CONVERT (varchar), GETDATE (), 109)
0512 2006 1

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

Select CONVERT (varchar), GETDATE (), 113)
12 052006

Select CONVERT (varchar), GETDATE (), 114)

SQL Server Date Function summary