SQL Server gets this week, this month, this year and other Time records

DateDiff (Week,zy_time,getdate ()) =0//query this week

DateDiff (Month,zy_time,getdate ()) =0//enquire this month

This season: SELECT * from table where DATEDIFF (Qq,c_calltime,getdate ()) =0
First half year 1-6, after half year 7-12:select * from table where DATEPART (mm,c_calltime)/7 = datepart (Mm,getdate ())/7

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
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 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), 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, as specified by interval date2-date1
DATEADD (Interval,number,date)	The date specified by interval, plus number
DatePart (Interval,date)	Returns the integer value that corresponds to the specified part of the date interval
Datename (Interval,date)	Returns the string name corresponding to the specified part of the date, interval

The setting values for the parameter interval are as follows:

Value	Abbreviation (SQL Server)	Access and ASP	Description
Year	Yy	yyyy	Year 1753 ~ 9999
Quarter	Qq	Q	Season 1 ~ 4
Month	Mm	M	Month 1 ~ 12
Day of the Year	Dy	Y	The number of days in a year, the day of the year 1-366
Day	Dd	D	Day, 1-31
Weekday	Dw	W	The number of days in a week, the day of the week 1-7
Week	Wk	Ww	Week, week of the year 0 ~ 51
Hour	Hh	H	Time 0 ~ 23
Minute	Mi	N	Minutes 0 ~ 59
Second	Ss	S	seconds 0 ~ 59
Millisecond	Ms	-	Milliseconds 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

SQL query this month issue

---for the number of days to select DateDiff (Day, ' 2004-01-01 ', GETDATE ())--1. One month first select DATEADD (mm, DateDiff (Mm,0,getdate ()), 0)--2. This week's Monday select DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0) Select DATEADD (Wk,datediff (Wk,0,getdate ()), 6)--3. First day of the year select DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)--4. First day of the quarter select DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)--5. Midnight Select of the day DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)--6. Last day of last month select DATEADD (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))- -7. Last day of last year select DATEADD (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))--8. Last day of the month Select DATEADD (Ms,-3,dateadd (mm , DATEDIFF (M,0,getdate ()) +1, 0))--9. The last day of this year select DATEADD (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))--10. This month's first Monday select DATEADD (Wk,datediff (Wk,0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)--query this week's number of registrants select Count (*) from [User]where DateDiff (Week,create_day-1,getdate ()) =0--last week registered number select COUNT (*) from [User]where DateDiff ( Week,create_day-1,getdate ()) =1--number of registrations this month select COUNT (*) from [User]where DateDiff (Month,create_Day,getdate ()) =0--last month registered number select COUNT (*) from [User]where DateDiff (Month,create_day,getdate ()) =1--if efficiency is to be Write queries like this--query the number of registrations this week select COUNT (*) from [User]where Create_day>=dateadd (Day,2-datepart (Weekday,getdate ()), CONVERT ( Varchar,getdate (), Create_day<dateadd (), (Day,9-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), 112)--Last week registration number select COUNT (*) from [User]where Create_day>=dateadd (Day,-5-datepart (Weekday,getdate ()), CONVERT ( Varchar,getdate (), Create_day<dateadd (), (Day,2-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), 112)--this month the number of registrants select COUNT (*) from [User]where Create_day>=dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate ( ) and Create_day<dateadd (Month,1,dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate (), 112))-- Last month registered number select COUNT (*) from [User]where Create_day>=dateadd (Month,-1,dateadd (Day,1-day (), GETDATE ()), CONVERT ( Varchar,getdate () (), ())) and Create_day<dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate (), 112))-- This week select COUNT (*) from USerwhere DateDiff (Dd,create_day,getdate ()) <= datepart (dw,getdate ())--Last week Select COUNT (*) from Userwhere DateDiff (DD , Create_day, (getdate ()-DatePart (Dw,getdate ())) <= 7--this month select COUNT (*) from Userwhere datepart (mm,create_day) = DatePart (Mm,getdate ())--Last month Select COUNT (*) from Userwhere datepart (mm,create_day) = DatePart (Mm,getdate ())-- This week select COUNT (*) from [User]where DateDiff (Dd,create_day,getdate ()) <= datepart (dw,getdate ())--Last week Select COUNT (*) From [User]where DateDiff (Dd,create_day, (getdate ()-DatePart (Dw,getdate ())) <= 7--this month select COUNT (*) from [User] where DATEPART (mm,create_day) = DatePart (Mm,getdate ())--Last month Select COUNT (*) from [User]where datepart (mm,create_day) = DatePart (Mm,getdate ())-1 Learning month (create_day) =month (getdate ()) month (Create_day) =month (GETDATE ())-1 added last month Query all today's SELECT * From Feedback WHERE (DATEDIFF (D,fedtime,getdate ()) =0) ORDER by Fedid DESC

　　

SQL Server gets this week, this month, this year and other Time records