SQL Server Date function

Reference:http://www.cnblogs.com/coconut_zhang/archive/2009/02/02/1382598.html

Http://blog.itpub.net/14766526/viewspace-1156100/

Select GETDATE () as ' current date ',
Datename (Year,getdate ()) as ' year ',
Datename (Month,getdate ()) as ' month ',
Datename (Day,getdate ()) as ' Day ',
Datename (Dw,getdate ()) as ' Week ',
Datename (Week,getdate ()) as ' Week number ',
Datename (Hour,getdate ()) as ' when ',
Datename (Minute,getdate ()) as ' min ',
Datename (Second,getdate ()) as ' s '

1. Current system date, time

Select GETDATE ()--2015-05-06 08:40:17.870

Take it off today and seconds.

SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)--2015-05-06 00:00:00.000　　　

DECLARE @ datetime
SET @ = GETDATE ()
SELECT @,dateadd (Day, DATEDIFF (day,0,@), 0)

today yesterday and tomorrow  

Select GETDATE () as Today--2015-05-06 07:36:06.957  

Select DATEADD (day,-1,getdate ())--2015-05-05 07:36:06.957  

Select DATEADD (Day,1,getdate ())--2015-05-07 07:36:06.957

Take the system date and separate the date
Select Current date =convert (varchar), DATEADD (Day,-1,getdate ()), 120), Current time =convert (varchar (8), GETDATE (), 114)-- Current date: 2015-05-06, current time: 08:26:07

Take the current week and day of the week

Select is currently the week =datename (Week,getdate ()), today is the week of =datename (Weekday,getdate ())--is currently the first week: 19, today is the week: Wednesday

Gets the current day of the week
Datename (Weekday, GETDATE ())

Show first day of the month
SELECT DATEADD (Mm,datediff (Mm,0,getdate ()), 0)
Select CONVERT (Datetime,convert (varchar (8), GETDATE (), 120) + ' 01 ', 120)

The first day of last month

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

Show last day of the month
Select DATEADD (Day,-1,convert (datetime,convert (varchar (8), DATEADD (Month,1,getdate ()), 120) + ' 01 ', 120))
SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (M,0,getdate ()) +1,0)

The last day of last month
SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (Mm,0,getdate ()), 0))

The first Monday of the month
Select DATEADD (Wk,datediff (wk,0, DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

First day of the year
SELECT DATEADD (Yy,datediff (Yy,0,getdate ()), 0)

The last day of the year
SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()) +1,0)

Last day of last year
SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()), 0))

The first day of the quarter
SELECT DATEADD (Qq,datediff (Qq,0,getdate ()), 0)

The night of the day

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

Monday of the Week
SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

Check this month's records
SELECT * from TableName where DATEPART (mm, thedate) = DATEPART (mm, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())

Query this week's records
SELECT * from TableName where DATEPART (wk, thedate) = DATEPART (wk, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())

Check the records for this season
SELECT * from TableName where DATEPART (QQ, thedate) = DATEPART (QQ, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())
Get total days in the month:
Select DATEDIFF (Dd,getdate (), DATEADD (mm, 1, GETDATE ()))

Get total number of days in a month

DECLARE @y varchar (8)
DECLARE @m int
Set @y= ' 2004 '
Set @m=2--month
Select DATEDIFF (day,@y+ '-' +cast (@m as varchar) + ' -15 ', @y+ '-' +cast (@m+1 as varchar) + '-15 ')--29

To query the record data between a date:
SELECT * FROM table where start time > ' 2005-02-01 ' and End time <= ' 2005-06-05 ' ORDER by id DESC

Query the record data in the last 30:
SELECT * FROM table where DATEDIFF (Dd, Date field, GETDATE ()) <=30 ORDER BY id DESC

Query for record data with Ctr greater than 100 in the last week:
SELECT * FROM table where hit_count>100 and DateDiff (Dw, Date field, GETDATE ()) <=7 ORDER BY id DESC

determines whether a leap year
SELECT case Day (DateAdd (Mm,2,dateadd (Ms,-3,dateadd ( YY, DATEDIFF (Yy,0,getdate ()), 0))) when the ' Common year ' else ' Leap Year ' end 
--or
Select Case DATEDIFF (Day,datename (ye Ar,getdate ()) + ' -02-01 ', DateAdd (Mm,1,datename (Year,getdate ()) + ' -02-01 ')
when "Then ' common year ' else ' Leap Year ' end

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 in the interval specified date2-date1
DATEADD (interval,number,date) with the date specified in interval, plus number
DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval
Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval
The setting values for the parameter interval are as follows:
value abbreviation (SQL Server) description
Year Yy 1753 ~ 9999
Quarter Qq Season 1 ~ 4
Month Mm 1 ~ 12
Day of the year Dy the number of days in the year, the day of the first 1-366
Day Dd, 1-31
Weekday Dw Number of days of the week, day of the week 1-7
Week Wk Week, week of the year 0 ~ 51
Hour Hh 0 ~ 23
Minute Mi minutes 0 ~ 59
Second Ss sec 0 ~ 59
Millisecond MS Ms 0 ~ 999

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

Select DateDiff (Day, ' 2004-09-18 ', ' 2004-09-01 ')--return: 17

Select DateDiff (Month, ' 1991-6-12 ', ' 1992-6-21 ')--return: 12

4. DatePart returns an integer representing the specified date portion of the specified date.

SELECT DATEPART (month, ' 2004-10-15 ')--returns 10, year, month, date, day, hour hour, divided into minute, second to second

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

5. Datename returns a string representing the specified date part of the specified date

Select Datename (Weekday, ' 2004-10-15 ') or select Datename (DW, ' 2004-10-15 ')--return: Friday

6. Day (), month (), year ()--can be compared with datepart

Select Day (' 2015-05-06 ') or select DATEPART (d, ' 2015-05-06 ')--return: 6

SQL Server Date function