MSSQL retrieval yesterday, this week, and this month

Note: Sunday is the first day of the week, not the last day of the week () minus one day, such as dateadd (day,-1, getDate () This week: select * fromtablewheredatediff (week, C_CALLTIME, getdat

Note: Sunday is the first day of the week, not the last day of the week () minus one day, such as dateadd ('day',-1, getDate () This week: select * from table where datediff (week, C_CALLTIME, getdat

Note: Sunday is the first day of the week, not the last day of the week () minus one day, such as dateadd ('day',-1, getDate ())

This week: select * from table where datediff (week, C_CALLTIME, getdate () = 0 -- C_CALLTIME is the Date Field

This month: Select * from table where datediff (Month, C_CALLTIME, getdate () = 0 -- C_CALLTIME is the Date Field

This season: select * from table where datediff (qq, C_CALLTIME, getdate () = 0

Select * from table where datepart (mm, C_CALLTIME)/7 = datepart (mm, getdate ()/7

Yesterday

Select convert (varchar (10), getdate ()-1,120)

Tomorrow

Select convert (varchar (10), getdate () + 1,120)

Last Seven Days

Select * from tb whereTimeField> = convert (varchar (10), getdate ()-7,120)

The next seven days

Select * from tb whereTimeField <= convert (varchar (10), getdate () + 7,120) andTimeField> =TimeField

You can use the convert and dateadd functions in combination.

Use datediff (day,TimeColumn, getdate ())

Last month

Select * from tb where month (TimeField) = month (getdate ()-1

This month

Select * from tb where month (TimeField) = month (getdate ())

Next month

Select * from tb where month (TimeField) = month (getdate () + 1

-- If you want to query partitions in a 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

-- The 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

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

This week

Select * from tb where datediff (week,TimeField, getdate () = 0

Last week

Select * from tb where datediff (week,TimeField, getdate () = 1

Next week

Select * from tb where datediff (week,TimeField, getdate () =-1

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

1. Now I need to get only the date part,TimeSome do not, how to write SQL?

Select convert (varchar (10), getdate (), 120)

2. Calculate the SQL statement of the following date:

Yesterday

Select convert (varchar (10), getdate ()-1,120)

Tomorrow

Select convert (varchar (10), getdate () + 1,120)

Last Seven Days

Select * from tb whereTimeField> = convert (varchar (10), getdate ()-7,120)

The next seven days

Select * from tb whereTimeField <= convert (varchar (10), getdate () + 7,120) andTimeField> =TimeField