SQL Server Common Date processing

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 '

Results:
2009-08-13 23:07:15.403 2009 08 13 Week 433 23 7 15

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


2. 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)


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


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


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


6. Last day of the year
SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()) +1,0)


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


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


9. This week's Monday
SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)


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


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


12. Check the records for this season
SELECT * from TableName where DATEPART (QQ, thedate) = DATEPART (QQ, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())
Where: GETDATE () is a function of acquiring system time.


13. Get the total number of days in the month:
Select DATEDIFF (Dd,getdate (), DATEADD (mm, 1, GETDATE ()))


Select DateDiff (Day,
DATEADD (mm, DateDiff (mm, ", GETDATE ()),"),
DATEADD (mm, DateDiff (mm, ", GETDATE ()), ' 1900-02-01 ')


14. Get the current day of the week
Datename (Weekday, GETDATE ())


15. Current system date, time
Select GETDATE ()


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


DateDiff returns the number of date and time boundaries across two specified dates.
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17


DatePart returns an integer representing the specified date portion of the specified date.
SELECT DATEPART (month, ' 2004-10-15 ')--return 10
Year, month, date is day, hour hour, divided into minute, seconds for second


Datename returns a string representing the specified date part of a specified date
SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday


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 this year how many weeks =datename (week, ' 2004-10-15 '), today is the week of =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 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


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


Examples of applications:


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,last_date,getdate ()) <=30 ORDER BY id DESC


Query for record data with Ctr greater than 100 in the last week:
SELECT * from T_business_product where hit_count>100 and DateDiff (Dw,last_date,getdate ()) <=7 ORDER BY id DESC


Query the record data for a year (for example, 2006):
SELECT * FROM table where DatePart (yy,last_date) =2006 ORDER BY id DESC
Or
SELECT * FROM table where DatePart (year,last_date) =2006 ORDER BY id DESC


such as querying the data for the year inserted in the current year of the system:
SELECT * FROM table where DatePart (Yy,getdate ()) =datepart (Yy,getdate ()) Order BY id DESC


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)
---------------------Date
Year (), month (), date ()

SQL Server Common Date processing