SQL Query Day, this month, this week's record

SELECT * FROM table WHERE convert (Nvarchar, DateAndTime, 111) = CONVERT (Nvarchar, GETDATE (), 111) ORDER by DateAndTime DESC

Record of the Month

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

Week record

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

Day record

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

time functions 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
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
---to find the difference in days
Select DateDiff (Day, ' 2004-01-01 ', GETDATE ())

--1. The first day of one months
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

--2. Monday of the Week
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

Select DATEADD (Wk,datediff (Wk,0,getdate ()), 6)
--3. The 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. The night of the day
SELECT 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 the year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))

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

--Check the number of people registered this week
Select COUNT (*) from [user]
where DateDiff (Week,create_day-1,getdate ()) =0

--Number of registrations last week
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

--Number of registrations last month
Select COUNT (*) from [user]
where DateDiff (Month,create_day,getdate ()) =1

-If you want to be efficient, write queries like this

--Check the number of people registered this week
Select COUNT (*) from [user]
where Create_day>=dateadd (Day,2-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), 112))
and Create_day<dateadd (Day,9-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), 112))

--Number of registrations last week
Select COUNT (*) from [user]
where Create_day>=dateadd (Day,-5-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), 112))
and Create_day<dateadd (Day,2-datepart (Weekday,getdate ()), CONVERT (Varchar,getdate (), 112))


--Number of registrations this month
Select COUNT (*) from [user]
where Create_day>=dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate (), 112))
and Create_day<dateadd (Month,1,dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate (), 112))

--Number of registrations last month
Select COUNT (*) from [user]
where Create_day>=dateadd (Month,-1,dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate (), 112)))
and Create_day<dateadd (Day,1-day (getdate ()), CONVERT (Varchar,getdate (), 112))

--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
--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
Learn
Month (create_day) =month (getdate ())
Month (create_day) =month (GETDATE ())-1 last month

Supplementary query today All
SELECT * FROM Feedback WHERE (DATEDIFF (D,fedtime,getdate ()) =0) ORDER by Fedid DESC

SQL takes records for the current day or month
The time format in the table is this: 2007-02-02 16:50:08.050, if the direct and the time of the day compared, there is no accurate data, but we can put this format of time [format] into the 2007-02-02, that is, only the year-month-day, Then the time of day is also formatted with the format of the adult-month-day.
In this way, the idea is out!
We format the date to use the CONVERT () function, to use 3 parameters, first to format the date of the day, Convert (varchar (), getDate (), 120)
So we can format the date of the day as: 2007-2-2, and then format the date in the database table
Convert (varchar), timefiled,120, and finally we can get the data for the day with an SQL statement.
For example:

Go from Network

Program code SELECT * FROM View_countbill Where convert (varchar (), [time],120) = Convert (varchar), getDate (), 120)

Attention:
The meaning of each parameter in the Convert () function, the first parameter, varchar (10) is the data type provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type. The second parameter is the field you want to convert, and here I am [TIME]. The last one is the format, the value is optional: 20 or 120 can be, it follows the [ODBC specification], the input/output style is: Yyyy-mm-dd HH:MM:SS[.FFF]
Specifically, you can refer to the online Help for SQL Server!

======================================================
The record for the current month in T-SQL lookup table
Idea: The time field to be looked up with the month () function, and then take out the month of the current month, the comparison is OK
Cases:

Program code SELECT * FROM View_countbill Where month ([time]) = month (GetDate ())