[Zhuan] SQL Server query last day, three days, week, January, first quarter method

Three days select * from T_news where DateDiff (Day,addtime,getdate ()) <= 2 and DateDiff (Day,addtime,getdate ()) >= 0

a week select * from T_news WHERE (DATEPART (wk, addtime) = DATEPART (wk, GETDATE ())) and (DATEPART (yy, addtime) = DATEPART ( YY, GETDATE ()))Note: You cannot use the DateDiff difference at this time as 7, because DateDiff only represents the number of intervalsJanuary SELECT * from T_news WHERE (DATEPART (yy, addtime) = DATEPART (yy, GETDATE ())) and (DATEPART (mm, addtime) = DATEPART ( MM, GETDATE ()))first Quarter select * from T_news where DATEPART (QQ, addtime) = DATEPART (QQ, GETDATE ()) and DATEPART (yy, addtime) = DATEPART (yy , 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

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

SQL Gets the date part of the current system time

CONVERT (varchar), getDate (), 120)

To get the "Yesterday, today" date function of SQL

Category: Ms-sql Server base class

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

?

Convert (Datetime,getdate (), 2)

GetDate () gets today's date 2007-03-26 16:14:12.187

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

2. Ask for the following date sql:

Yesterday

Tomorrow

Last seven days

Next seven days

Last week

Week

Next week

Last month

Month

Next month

Please help the master. Thank you

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

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

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

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

--1.

Select Convert (Varchar), GetDate (), 120)

Select Convert (Varchar), GetDate (), 121)

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

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

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

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

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

--2

-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

Last month