SQL Server--query one day, one week, one month record (DateDiff function) (memo)

Learn From: http://bjtdeyx.iteye.com/blog/1447300

 

The most common SQL date query statement

 --Query data on the first anniversary of the current day
select * from ShopOrder where datediff (week, ordTime, getdate ()-1) = 0
 --Query all data for the day
select * from ShopOrder where datediff (day, ordTime, getdate ()-1) = 0
 

--info is the table name, datetime is the field value in the database

--Inquiry 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

-Inquiry about alternative methods of recording the same day
SELECT *
FROM j_GradeShop
WHERE (GAddTime BETWEEN CONVERT (datetime, LEFT (GETDATE (), 10) + ’00: 00: 00.000’)
AND CONVERT (datetime, LEFT (GETDATE (), 10) + ’00: 00: 00.000’) + 1)
ORDER BY GAddTime DESC
 

DATEDIFF function:

Syntax:

DATEDIFF (datepart, startdate, enddate)
Remarks: enddate minus startdate. If startdate is later than enddate, a negative value is returned.

If the result exceeds the range of integer values, DATEDIFF will generate an error. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes, and 23.647 seconds. For seconds, the maximum number is 68 years.

The method of calculating across boundaries such as minutes, seconds, and milliseconds makes the results specified by DATEDIFF consistent across all data types. The result is a signed integer value, which 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:

Sql code

--Record this month
SELECT * FROM table WHERE datediff (month, [dateadd], getdate ()) = 0

--Record this week
SELECT * FROM table WHERE datediff (week, [dateadd], getdate ()) = 0

-Including this year's query method is the same
 

Time function in sql server


1. Current system date and time

select getdate ()

2. dateadd adds a period of time to the specified date and returns a new datetime value
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 part 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

select current date = convert (varchar (10), 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 the day of the week = datename (weekday, ’2004-10-15’)
 
Note: function parameter / function

GetDate () returns the current date and time of the system
DateDiff (interval, date1, date2) returns the difference between date2 and date1 in the manner specified by interval date2-date1
DateAdd (interval, number, date) In the way specified by interval, add the date after number
DatePart (interval, date) returns the integer value corresponding to the specified part of interval in date date
DateName (interval, date) returns the string name corresponding to the specified part of interval in the date date
 

The setting values of the parameter interval are as follows:


Value Abbreviation (Sql Server) Access and ASP Instructions

Year Yy yyyy Year 1753 ~ 9999
Quarter Qq q quarter 1 ~ 4
Month Mm m month 1 ~ 12
Day of year Dy y Day of the year, the day of the year 1-366
Day Dd d Day, 1-31
Weekday Dw w Days of the week, day of the week 1-7
Week Wk ww Week, the week of the year 0 ~ 51
Hour Hh h hour 0 ~ 23
Minute Mi n minutes 0 ~ 59
Second Ss s seconds 0 ~ 59
Millisecond Ms-milliseconds 0 ~ 999
Access and asp use date () and now () to obtain the system date and time; which DateDiff, DateAdd, DatePart can also be used in Access and asp, these functions are also used


Examples:

1.GetDate () for sql server: select GetDate ()

2. DateDiff (’s’, ’2005-07-20’, ’2005-7-25 22:56:32’) returns 514592 seconds
DateDiff (’d’, ’2005-07-20’, ’2005-7-25 22:56:32’) returns 5 days

3.DatePart (’w’, ’2005-7-25 22:56:32’) The return value is 2 that is Monday (Sunday is 1, Saturday is 7)
DatePart (’d’, ’2005-7-25 22:56:32’) returns 25 or 25
DatePart (’y’, ’2005-7-25 22:56:32’) returns 206, which is the 206th day of the year
DatePart (’yyyy’, ’2005-7-25 22:56:32’) return value is 2005 or 2005
 

sqlserver-Query one day, one week, one month records (DateDiff function) (Memo)