SQL Server Date function application

1. Get the current time

SELECT GETDATE () as ' current date ', Datename (year, GETDATE ()) as ' Years ', datename (month, GETDATE ()) as ' month ',        Datename (Day, GETDATE ()) as ' Days ', Datename (DW, GETDATE ()) as ' Week ', Datename (week, GETDATE ()) as ' Week number ', Datename (Hour, GETDATE ()) as ' time ', datename (minute, GETDATE ()) as ' Sub ', Datename (second, GETDATE ()) as ' sec ‘

Returns results such as

2.

--1. Show first day of the month

SELECT DATEADD (Mm,datediff (Mm,0,getdate ()), 0)

Select CONVERT (Datetime,convert (varchar (8), GETDATE (), 120) + ' 01 ', 120)

--2. Show last day of the month

Select DATEADD (Day,-1,convert (datetime,convert (varchar (8), DATEADD (Month,1,getdate ()), 120) + ' 01 ', 120))

SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (M,0,getdate ()) +1,0)

--3. Last day of last month

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

--4. First Monday of the month

Select DATEADD (Wk,datediff (wk,0, DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

--5. First day of the year

SELECT DATEADD (Yy,datediff (Yy,0,getdate ()), 0)

--6. The last day of the year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()) +1,0)

--7. Last day of last year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()), 0))

--8. The first day of the quarter

SELECT DATEADD (Qq,datediff (Qq,0,getdate ()), 0)

--9. Monday of the Week

SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

--10. Querying this month's records

SELECT * from TableName where DATEPART (mm, thedate) = DATEPART (mm, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())

--11. Querying this week's records

SELECT * from TableName where DATEPART (wk, thedate) = DATEPART (wk, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())

--12. Check the records for this season

SELECT * from TableName where DATEPART (QQ, thedate) = DATEPART (QQ, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ())

-Where: GETDATE () is a function of acquiring system time.

13. Get the total number of days in the month:

Select DATEDIFF (Dd,getdate (), DATEADD (mm, 1, GETDATE ()))

Select DateDiff (Day,

DATEADD (mm, DateDiff (mm, ", GETDATE ()),"),

DATEADD (mm, DateDiff (mm, ", GETDATE ()), ' 1900-02-01 ')

14. Get the current day of the week

Select Datename (Weekday, GETDATE ())

15. Current system date, time

Select GETDATE ()

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

DateDiff returns the number of date and time boundaries across two specified dates.

Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

DatePart returns an integer representing the specified date portion of the specified date.

SELECT DATEPART (month, ' 2004-10-15 ')--return 10

Year, month, date is day, hour hour, divided into minute, seconds for second

Datename returns a string representing the specified date part of a specified date

SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday

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 this year how many weeks =datename (week, ' 2004-10-15 '), today is the week of =datename (weekday, ' 2004-10-15 ')

function parameters/Functions

GetDate () returns the current date and time of the system

DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates in the interval specified date2-date1

DATEADD (interval,number,date) with the date specified in interval, plus number

DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval

Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval

The setting values for the parameter interval are as follows:

value abbreviation (SQL Server) description

Year Yy 1753 ~ 9999

Quarter Qq Season 1 ~ 4

Month Mm 1 ~ 12

Day of the year Dy the number of days in the year, the day of the first 1-366

Day Dd, 1-31

Weekday Dw Number of days of the week, day of the week 1-7

Week Wk Week, week of the year 0 ~ 51

Hour Hh 0 ~ 23

Minute Mi minutes 0 ~ 59

Second Ss sec 0 ~ 59

Millisecond MS Ms 0 ~ 999

Example:

1.GetDate () for SQL Server:select GetDate ()

2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds

DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days

3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7)

DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th

DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year

DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005

Examples of applications:

To query the record data between a date:

SELECT * FROM table where start time > ' 2005-02-01 ' and End time <= ' 2005-06-05 ' ORDER by id DESC

Query the record data in the last 30:

SELECT * FROM table where DATEDIFF (Dd,last_date,getdate ()) <=30 ORDER BY id DESC

Query for record data with Ctr greater than 100 in the last week:

SELECT * from T_business_product where hit_count>100 and DateDiff (Dw,last_date,getdate ()) <=7 ORDER BY id DESC

Query the record data for a year (for example, 2006):

SELECT * FROM table where DatePart (yy,last_date) =2006 ORDER BY id DESC

Or

SELECT * FROM table where DatePart (year,last_date) =2006 ORDER BY id DESC

such as querying the data for the year inserted in the current year of the system:

SELECT * FROM table where DatePart (Yy,getdate ()) =datepart (Yy,getdate ()) Order BY id DESC

Take the system date and separate the date

Select Current date =convert (varchar), DATEADD (Day,-1,getdate ()), 120), Current time =convert (varchar (8), GETDATE (), 114)

---------------------Date

Year (), month (), date ()

SQL Server Date function application