Reproduced Date calculations in SQL statements

1. First day of the month
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

2. Last day of the month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

3. The first day of the month
Select DATEADD (m,-1, DateAdd (mm, DATEDIFF (Mm,0,getdate ()), 0))

4. Last day of last month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

5. This week's Monday
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

6. This week's Sunday
Select DATEADD (d,+6, DateAdd (DATEDIFF (), WK, Wk,0,getdate ()), 0)

7. Last week's Monday
Select DATEADD (d,-7, DateAdd (DATEDIFF (), WK, Wk,0,getdate ()), 0)

8. Last week's Sunday
Select DATEADD (d,-1, DateAdd (DATEDIFF (), WK, Wk,0,getdate ()), 0)

9. The first day of the year
SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

10. First day of the quarter
SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

11. Last day of last year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))

12. First Monday of the month
Select DATEADD (wk, DATEDIFF (Wk,0,dateadd (Dd,6-datepart (day,getdate)), getdate)), 0)

13. Last day of the year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0)).

Date and time functions in SQL Server
1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value based on adding a period of time to the specified date
Example: Add 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 across two specified dates.
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

4. DatePart returns an integer representing the specified date portion of the specified date.
SELECT DATEPART (month, ' 2004-10-15 ')--return 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 ()--can be compared with datepart

Select Current date =convert (varchar), GETDATE (), 120)
, Current time =convert (varchar (8), GETDATE (), 114)

Select Datename (DW, ' 2004-10-15 ')

Select how many weeks of the year =datename (week, ' 2004-10-15 ')
, today is a few weeks =datename (weekday, ' 2004-10-15 ')

http://www.52186.cn/nibablog/blogview.asp?logID=333

SQL Date format conversion

--------------------------------------------------------------------------------
Source: CSDN: New book City Collection release Date: 2006-7-31 popularity: 384


In SQL, smalldatetime and datetime data display is not so satisfying, so I looked up some formatting methods to list "

Convert (char (), datetime,101)

When the database is taken out, the conversion is good.
Select GETDATE ()

2006-05-12 11:06:08.177

I've tidied up the date format conversion methods that you might often use in SQL Server:

Examples are as follows:
Select Convert (varchar), GETDATE (), 120)

2006-05-12
Select CONVERT (varchar, GETDATE (), 120)
2006-05-12 11:06:08

Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ', '), ': ', ')
20060512110608

Select CONVERT (varchar), GETDATE (), 111)
2006/05/12

Select CONVERT (varchar), GETDATE (), 112)
20060512

Select CONVERT (varchar), GETDATE (), 102)
2006.05.12

Several other infrequently used date format conversion methods:

Select CONVERT (varchar), GETDATE (), 101)
0612/2005 Select CONVERT (varchar (), GETDATE (), 103)
12/09/2004

Select CONVERT (varchar), GETDATE (), 104)
12.05.2006

Select CONVERT (varchar), GETDATE (), 105)
12-05-2006

Select CONVERT (varchar), GETDATE (), 106)
12 05 2006

Select CONVERT (varchar), GETDATE (), 107)
05 12, 2006

Select CONVERT (varchar), GETDATE (), 108)
11:06:08

Select CONVERT (varchar), GETDATE (), 109)
0512 2006 1

Select CONVERT (varchar), GETDATE (), 110)
09-12-2004

Select CONVERT (varchar), GETDATE (), 113)
12 052006

Select CONVERT (varchar), GETDATE (), 114)
11:06:08.177

============================================================================================
Example Walkthrough

SELECT * FROM SALESMB
Select COUNT (*) from Salesdreport where employeeid= ' and productsid= ' 7 ' and (UpdateTime between ' 2006-8-1 00:00:00 ' and ' 2006-8-30 00:00:00 ')--Sales quantity
Select SUM (sales) from SALESMB where eid= ' and productsid= ' 7 ' and (Mbrq between ' 2006-8-1 00:00:00 ' and ' 2006-8-30 00:00: 00 ')--number of targets

Select DATEADD (D,-day (getdate ()), GETDATE ())

Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-1 00:00 '
Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-' +cast (Day (GETDATE ()) as varchar)

Select DATEADD (M,1,dateadd (D,-day (getdate ()), GETDATE ())

Select DATEADD (M,1,getdate ())

=============================================================================================================== ======================================

SELECT * FROM SALESMB
Select COUNT (*) from Salesdreport where employeeid= ' and productsid= ' 7 ' and (UpdateTime between ' 2006-8-1 00:00:00 ' and ' 2006-8-30 00:00:00 ')--Sales quantity
Select SUM (sales) from SALESMB where eid= ' and productsid= ' 7 ' and (Mbrq between ' 2006-8-1 00:00:00 ' and ' 2006-8-30 00:00: 00 ')--number of targets

Select DATEADD (D,-day (getdate ()), GETDATE ())

Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-1 00:00 '
Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-' +cast (Day (GETDATE ()) as varchar)

Select DATEADD (M,1,dateadd (D,-day (getdate ()), GETDATE ())

Select DATEADD (M,1,getdate ())

=============================================================================================================== =======================================

SELECT * FROM SALESMB
Select COUNT (*) from Salesdreport where employeeid= ' and productsid= ' 7 ' and (UpdateTime between ' 2006-8-1 00:00:00 ' and ' 2006-8-30 00:00:00 ')--Sales quantity
Select SUM (sales) from SALESMB where eid= ' and productsid= ' 7 ' and (Mbrq between ' 2006-8-1 00:00:00 ' and ' 2006-8-30 00:00: 00 ')--number of targets

Select DATEADD (D,-day (getdate ()), GETDATE ())

Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-1 00:00 '
Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-' +cast (Day (GETDATE ()) as varchar)

Select DATEADD (M,1,dateadd (D,-day (getdate ()), GETDATE ())

Select DATEADD (M,1,getdate ())

=============================================================================================================== ================

Select GETDATE ()
Select DATEADD (D,1,dateadd (D,-day (getdate ()), GETDATE ()) + "00:00"

DECLARE @sdate smalldatetime
Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-1 00:00 '
Select @sdate
Select DATEADD (Day,-1,dateadd (month,1, @sdate))

Select CAST (Year (GETDATE ()) as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-1 00:00 '

Select DATEADD (D,-day (getdate ()), GETDATE ())

Select DATEADD (Day,-1,dateadd (Month,1,cast (getdate () as nvarchar) + '-' +cast (Month (GETDATE ()) as varchar) + '-1 00:00 '))

Select DATEADD (D,-day (getdate ()), GETDATE ())

Select DATEADD (D,1,dateadd (D,-day (getdate ()), GETDATE ())
Select DATEADD (M,

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))
SELECT DateAdd (Ms,0,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

Original source : http://www.cnblogs.com/qfb620/archive/2008/02/26/1082644.html

Reproduced Date calculations in SQL statements