Complete collection of SQL Server date functions

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:

System function
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 Qq 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

Program code
Select * From VIEW_CountBill Where Convert(varchar(10),[time],120) = Convert(varchar(10),getDate(),120)


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:

Program code
Select * From VIEW_CountBill Where Month([time]) = Month(getDate())

Tags: SQL Server

Posted by jodyanne on Tue, 10 May 2022 04:28:41 +0300