Use SQL to query the day, week, one months of data

SQL query day, week, one months of data   data query, whether on the site or the system, are very common, the following is the most common in the DATE query statement select * FROM Shoporder where DateDiff (Week,ordtime, GETDATE ()-1) =0  //Check the date of the day on the first anniversary of the data  select * from Shoporder where DateDiff (Day,ordtime,getdate ()-1) =0  // Query the day for all data  --query day:  select * from info where DateDiff (dd,datetime,getdate ()) =0  --query:  within 24 hours SELECT * FROM info where DateDiff (hh,datetime,getdate ()) <=24  --info is the table name and datetime is the field value in the database  -- Query Day: SELECT * from info where DateDiff (dd,datetime,getdate ()) =0 --query within 24 hours: SELECT * FROM info where DateDiff (hh, Datetime,getdate ()) <=24 --info is the table name, DateTime is the field value in the database  sql code--Query the day record alternative method  select * from j_ Gradeshop where (Gaddtime between CONVERT (DateTime, Left (GETDATE (), ten) + ' 00:00:00.000 ')  and convert ( DateTime, Left (GETDATE (), ten) + ' 00:00:00.000 ') + 1)  order by gaddtime desc --Query Day record alternative method select *from J_gradesh Opwhere (Gaddtime between CONVERT (DateTime, Left (GETDATE (), 10) + ' 00:00:00.000 ') and CONVERT (datetime, Left (GETDATE (), ten) + ' 00:00:00.000 ') + 1) ORDER by gaddtime Desc datediff function:  Syntax:  d Atediff (DatePart, StartDate, EndDate)   remarks: EndDate minus StartDate. If StartDate is later than EndDate, a negative value is returned.   DATEDIFF will produce an error if the result is outside the integer value range. For milliseconds, the maximum number is 24 days and 20 hours 31分钟零23.647秒. For seconds, the maximum number is 68 years.   A method that calculates boundaries across minutes, seconds, and milliseconds allows the results specified by DATEDIFF to be consistent across all data types. The result is a signed integer value that is equal to the number of datepart boundaries across the first and second days. For example, the number of weeks between January 4 (Sunday) and January 11 (Sunday) is 1.   can be tested in MSSQL: &NBSP;SQL code-two time difference is just the way 24 --print  print dateDiff (hh, ' 2009-1-1 0:0:0 ', ' 2009-1-2 0:0:0 ')   --Query Way  print dateDiff (hh, ' 2009-1-1 0:0:0 ', ' 2009-1-2 0:0:0 ')  --two time difference is exactly the way 24--printed print DateDiff (hh, ' 2009-1-1 0:0:0 ', ' 2009-1-2 0:0:0 ')  --query the way print DateDiff (hh, ' 2009-1-1 0:0:0 ', ' 2009-1-2 0:0:0 ')  sql code--this month records  select * FROM table WHERE DateDiff (Month,[dateadd],getdate ()) =0  --this week records  select * From table where DateDiff (Week,[dateadd],getdate ()) =0  --includes this year these query methods are the same  --this month records select * FROM table WHERE DateDiff (Month,[dateadd],getdate ()) =0 --this week records select * FROM table WHERE DateDiff (Week,[dateadd],getdate ()) =0  The time function in SQL Server  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  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 to 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 (),  select datename (DW, ' 2004-10-15 ')  select this year how many weeks =datename (week, ' 2004-10-15 '), today is the day of the week = Datename (Weekday, ' 2004-10-15 ')   function parameters/functions 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-date1dateadd (interval,number,date) in interval-specified manner, plus the date after number datepart (interval, date) returns the integer value Datename (interval,date) that corresponds to the specified part of the date, interval returns the string name   parameter for the specified part of the date, interval Interval set values are as follows:  value abbreviation (Sql Server) Access and ASP description year Yy yyyy 1753 ~ 9999Quarter Qq q Season 1 ~ 4Month Mm m month 1 ~ 12Day Of year Dy y number of days of the year, day 1-366day Dd D, 1-31weekday Dw W Day of the week, day of the week 1-7week Wk WW Week, week of the year 0 ~ 51Hour Hh h 0 ~ 23M Inute Mi N minutes 0 ~ 59Second Ss s seconds 0 ~ 59Millisecond ms-msec 0 ~ 999 access and ASP Use Date () and now () to get the system date time; where Datediff,dat Eadd,datepart can also be used in Access and ASP, and 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 ') with a return 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 that is 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 ') returns a value of 2005 that is 2005

Transferred from: http://www.2cto.com/database/201305/207629.html

Use SQL to query the day, week, one months of data