1, Statistical statement
1. -- statistics of current [> data after 00:00 of the day]
SELECT * FROM surface WHERE CONVERT(Nvarchar, dateandtime, 111) = CONVERT(Nvarchar, GETDATE(), 111) ORDER BY dateandtime DESC
2. -- statistics this week
SELECT * FROM surface WHERE datediff(week,[dateadd],getdate())=0
3. -- statistics of this month
SELECT * FROM surface WHERE datediff(month,[dateadd],getdate())=0
4. Statistics current
SELECT * FROM surface WHERE datediff(day,[dateadd],getdate())=0
Select * From table with(nolock) Where Convert(varchar(10),[CreateTime],120) = Convert(varchar(10),getDate(),120)
2, Time function
1. Current system date and time
select getdate()
2. dateadd returns a new datetime value based on adding a period of time to the specified date, for example, adding 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 that span 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 to 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() -- you can compare it with datepart
select current date=convert(varchar(10),getdate(),120), select current time =convert(varchar(8),getdate(),114), select datename(dw,'2004-10-15') select What week of the year=datename(week,'2004-10-15'), select What day is it today=datename(weekday,'2004-10-15')
7. Calculate the difference days
select datediff(day,'2004-01-01',getdate())
8. On the first day of a month
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
9. Monday of this week
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) select dateadd(wk,datediff(wk,0,getdate()),6)
10. The first day of the year
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
11. First day of the quarter
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
12. In the middle of the night
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
13. Last day of last month
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
14. Last day of last year
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
15. The last day of the month
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
16. The last day of the year
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
17. The first Monday of this month
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
18. Check the number of registrants this week
select count(*) from [user] where datediff(week,create_day-1,getdate())=0
19. Number of registrations last week
select count(*) from [user] where datediff(week,create_day-1,getdate())=1
20. Number of registrants this month
select count(*) from [user] where datediff(month,create_day,getdate())=0
21. Number of people registered last month
select count(*) from [user] where datediff(month,create_day,getdate())=1
If you want efficiency, use the following methods
22. Check the number of registrants 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))
23. Number of people registered 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))
24. Number of registered persons 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)))
25. Number of people registered 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))
26. This week
select count(*) from User where datediff(dd,create_day,getdate()) <= datepart(dw,getdate())
27. Last week
select count(*) from User where datediff(dd,create_day,(getdate() - datepart(dw,getdate()))) <= 7
28. Current month
select count(*) from User where datepart(mm,create_day) = datepart(mm,getdate())
29. Last month
select count(*) from User where datepart(mm,create_day) = datepart(mm,getdate()) - 1
30. Number of registrants this week
select count(*) from [User] where datediff(dd,create_day,getdate()) <= datepart(dw,getdate())
31. Number of people registered last week
select count(*) from [User] where datediff(dd,create_day,(getdate() - datepart(dw,getdate()))) <= 7
32. Number of registered persons this month
select count(*) from [User] where datepart(mm,create_day) = datepart(mm,getdate())
33. Number of people registered last month
select count(*) from [User] where datepart(mm,create_day) = datepart(mm,getdate()) - 1
34. Query all today
SELECT * from feedback WHERE (DATEDIFF(d,fedtime,GETDATE())=0) ORDER BY fedid DESC
month(create_day)=month(getdate()) this month
month(create_day)=month(getdate())-1 last month
All today's data: select * from ， table name ， where DateDiff(dd,datetime type field, getdate())=0
All data of yesterday: select * from ， table name ， where DateDiff(dd,datetime type field, getdate())=1
All data within 7 days: select * from table name "where DateDiff(dd,datetime type field, getdate()) < = 7
All data within 30 days: select * from table name "where DateDiff(dd,datetime type field, getdate()) < = 30
All data of this month: select * from ， table name ， where DateDiff(mm,datetime type field, getdate())=0
All data of this year: select * from ， table name ， where DateDiff(yy,datetime type field, getdate())=0
System functions:
| function | Parameters / functions |
| GetDate( ) | Returns the current date and time of the system |
| DateDiff (interval,date1,date2) | Returns the difference between date2 and date1 in the way specified by interval, date2-date1 |
| DateAdd (interval,number,date) | Add the date after number in the way specified by interval |
| DatePart (interval,date) | Returns the integer value corresponding to the specified part of interval in the date |
| DateName (interval,date) | Returns the string name corresponding to the specified part of interval in date |
Set value of parameter interval:
| value | Abbreviation (Sql Server) | Access and ASP | explain |
| Year | Yy | yyyy | 1753 ~ 9999 |
| Quarter | q | Season 1 ~ 4 | |
| Month | Mm | m | January to December |
| Day of year | Dy | y | Days of the year, the day of the year 1-366 |
| Day | Dd | d | Day, 1-31 |
| Weekday | Dw | w | Days of the week, the 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 get the system date and time; DateDiff, DateAdd and datepart can also be used in access and asp, and the usage of these functions is similar
give an example:
1.GetDate() be used for sql server :select GetDate()
2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')The return value is 514592 seconds
DateDiff('d','2005-07-20','2005-7-25 22:56:32')The return value is 5 days
3.DatePart('w','2005-7-25 22:56:32')The return value is 2, that is, Monday(Sunday is 1 and Saturday is 7)
DatePart('d','2005-7-25 22:56:32')The return value is 25, i.e. No. 25
DatePart('y','2005-7-25 22:56:32')The return value is 206, that is, the 206 day of the year
DatePart('yyyy','2005-7-25 22:56:32')The return value is 2005
Sql fetches the records of the current day or current month
The time format in the table is as follows: 2007-02-02 16:50:08.050. If it is directly compared with the time of the day, we can not get accurate data. However, we can format the time in this format into 2007-02-02, that is, only year month day, and then format the time of the day into adult month day format
In this way, the idea comes out!
We need to use the function Convert() to format the date. We need to use three parameters. First, format the date of the day, Convert(varchar(10),getDate(),120)
In this way, we can format the date of the day as: 2007-2-2, and then format the date in the database table
Convert (varchar (10), timefile, 120). Finally, we can use a Sql statement to get the data of the day
For example:
Transfer from network
be careful:
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. User defined data types cannot be used. The second parameter is the field you want to convert. Here is [time]. The last one is the format. This value is optional: 20 or 120. It follows the [ODBC Specification], and the input / output style is: yyyy MM DD HH: mm: SS [. FFF]
For details, please refer to the online help of Sql Server!
======================================================
The records of the current month in the T-Sql lookup table
Idea: use the Month() function to take out the month in the time field to be searched, and then take out the month of the current month. The comparison is OK
Example: