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 ())