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

Source: Internet
Author: User
Tags sql server query

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.