SQL Query day, week, month record

SQL Query day, week, month record
--Day of enquiry:
[SQL] View plaincopyprint?
SELECT * FROM info where DateDiff (dd,datetime,getdate ()) =0
--Query within 24 hours:
[SQL] View plaincopyprint?
SELECT * FROM info where DateDiff (hh,datetime,getdate ()) <=24
--info is the table name, DateTime is the field value in the database
--Day of enquiry:
[SQL] View plaincopyprint?
SELECT * FROM table where DateDiff (Dd,datetime,getdate ()) =0--Query within 24 hours: SELECT * FROM table where DateDiff (hh,datetime,get Date ()) <=24
--table is the table name, DateTime is the field value in the database

DATEDIFF function:
Grammar:
[SQL] View plaincopyprint?
SELECT * FROM table where DateDiff (Dd,datetime,getdate ()) =0--Query within 24 hours: SELECT * FROM table where DateDiff (hh,datetime,get Date ()) <=24
Note:
EndDate minus StartDate. If StartDate is later than EndDate, a negative value is returned.
If the result is outside the integer value range, DATEDIFF will produce an error. For milliseconds, the maximum number is 24 days and 20 hours 31分钟零23.647秒. For seconds, the maximum number is 68 years.
Methods that are computed across boundaries such as minutes, seconds, and milliseconds make the result specified by DATEDIFF 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.
[SQL] View plaincopyprint?
--Record of the month
SELECT * FROM table WHERE DateDiff (Month,[dateadd],getdate ()) =0

--This week's record
SELECT * FROM table WHERE DateDiff (Week,[dateadd],getdate ()) =0
--including this year, these query methods are the same
[SQL] View plaincopyprint?
--Record of the month

SELECT * FROM table WHERE DateDiff (Month,[dateadd],getdate ()) =0

--This week's record

SELECT * FROM table WHERE DateDiff (Week,[dateadd],getdate ()) =0

--including this year, these query methods are the same

Time functions in SQL Server
1. Current system date, time
[SQL] View plaincopyprint?
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
[SQL] View plaincopyprint?
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.
[SQL] View plaincopyprint?
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.
[SQL] View plaincopyprint?
SELECT DATEPART (month, ' 2004-10-15 ')--return 10
5. Datename returns a string representing the specified date part of the specified date
[SQL] View plaincopyprint?
SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday
6. Day (), month (), year ()--can be compared with datepart
[SQL] View plaincopyprint?
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 a few weeks =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 abbreviations (SQL Server) Access and ASP descriptions
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

In Access and ASP, date () and now () are used to obtain system datetime, where Datediff,dateadd,datepart is also used in Access and ASP, and the usage 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 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

The existence of a judgment table does not exist:
[SQL] View plaincopyprint?
Select COUNT (*) from sysobjects where type= ' U ' and name= ' your table name '
The Judgment field does not exist:
[SQL] View plaincopyprint?
Select COUNT (*) from syscolumns
WHERE id = (select id from sysobjects where type= ' u ' and name= ' your table name ')
and name = ' The field name you want to judge '

SQL Current Date Acquisition tips
SQL script for the first day of one months:

[SQL] View plaincopyprint?
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

Monday of the Week

[SQL] View plaincopyprint?
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

The first day of the year

[SQL] View plaincopyprint?
SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

First day of the quarter

[SQL] View plaincopyprint?
SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

The night of the day

[SQL] View plaincopyprint?
SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

The last day of last month

[SQL] View plaincopyprint?
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))
Source:

SQL Query day, week, month record