MSSQL gets yesterday, this week, this month ...

Special instructions under: The following statistics this week data, Sunday is as the first day of next week, and not the last day of the week, so you put Sunday as the last day of the week, you need to getDate () on the basis of the reduction of one day, such as DATEADD (' Day ',-1, getDate ())

This week: SELECT * FROM table where DATEDIFF (Week,c_calltime,getdate ()) =0--c_calltime as Date field

This month: SELECT * FROM table where DATEDIFF (Month,c_calltime,getdate ()) =0--c_calltime as Date field

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

Yesterday

Select CONVERT (varchar), GETDATE ()-1,120)

Tomorrow

Select CONVERT (varchar), GETDATE () + 1,120)

Last seven days

SELECT * from TB where Time field >= convert (varchar), GETDATE ()-7,120)

Next seven days

SELECT * from TB where Time field <= convert (varchar (ten), GETDATE () + 7,120) and Time field >= Time field

The convert and DATEADD functions are used together.

With DateDiff (Day, Time column, GETDATE ())

Last month

SELECT * FROM TB where month (time field) = Month (getdate ())-1

Month

SELECT * FROM TB where month (time field) = Month (getdate ())

Next month

SELECT * FROM TB where month (time field) = Month (getdate ()) + 1

-If you are looking in the table

--Yesterday

Select * from TableName Where DateDiff (DD, Datetimcol, GetDate ()) = 1

-Tomorrow

Select * from TableName Where DateDiff (DD, GetDate (), datetimcol) = 1

--Last seven days

Select * from TableName Where DateDiff (DD, Datetimcol, GetDate ()) <= 7

--Next seven days

Select * from TableName Where DateDiff (DD, GetDate (), Datetimcol) <= 7

--Last week

Select * from TableName Where DateDiff (wk, Datetimcol, GetDate ()) = 1

--This week

Select * from TableName Where DateDiff (wk, Datetimcol, GetDate ()) = 0

--Next week

Select * from TableName Where DateDiff (wk, GetDate (), datetimcol) = 1

--Last month

Select * from TableName Where DateDiff (mm, Datetimcol, GetDate ()) = 1

--This month

Select * from TableName Where DateDiff (mm, Datetimcol, GetDate ()) = 0

--Next month

Select * from TableName Where DateDiff (mm, GetDate (), datetimcol) = 1

--------------------------------------------------------

Week

SELECT * from TB where DATEDIFF (week, Time field, GETDATE ()) = 0

Last week

SELECT * from TB where DATEDIFF (week, Time field, GETDATE ()) = 1

Next week

SELECT * from TB where DATEDIFF (week, Time field, GETDATE ()) = 1

--------------------------------------------------------

1. Now I need to get just the date part, time part not, SQL how to write?

Select CONVERT (varchar), GETDATE (), 120)

2. Ask for the following date sql:

Yesterday

Select CONVERT (varchar), GETDATE ()-1,120)

Tomorrow

Select CONVERT (varchar), GETDATE () + 1,120)

Last seven days

SELECT * from TB where Time field >= convert (varchar), GETDATE ()-7,120)

Next seven days

SELECT * from TB where Time field <= convert (varchar (ten), GETDATE () + 7,120) and Time field >= Time field

MSSQL gets yesterday, this week, this month ...