SQL query statements for this year, month, and today, with SQL date functions _mssql

Source: Internet
Author: User
Tags date1 datetime getdate time 0

Check this diary record

SELECT * FROM Messages WHERE convert (Nvarchar, createdate, MR) = CONVERT (Nvarchar, GETDATE (),) Order by CreateD Ate DESC

SELECT * from table WHERE DateDiff (day, column name , GETDATE ()) =0

Check this year

SELECT Count (*) from Messages WHERE DateDiff (year,createdate,getdate ()) =0

Query this month

SELECT Count (*) from Messages WHERE DateDiff (month,createdate,getdate ()) =0

This week 's record

SELECT * from table name WHERE datediff (week, column name , GETDATE ()) =0

SQL Date function
1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date
For example: Add 2 days to date
Select DATEADD (day,2, ' 2004-10-15′) – Back: 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 part 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′)-back: 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 the week =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, as specified in interval date2-date1
DATEADD (Interval,number,date) In the manner specified in interval, plus the date after number
DatePart (Interval,date) Returns the integer value of the specified part in date interval
Datename (Interval,date) Returns the string name of the specified section in date interval

The set values for the parameter interval are as follows:

Value Abbreviation (SQL Server) Access and ASP Description
Year Yy yyyy Year 1753 ~ 9999
Quarter Qq Q Quarter 1 ~ 4
Month Mm M Month 1 ~ 12
Day of the Year Dy Y The number of days in a year, the first day of the year 1-366
Day Dd D Day, 1-31
Weekday Dw W The number of days in a week, the day ordinal of a week, 1-7
Week Wk Ww Weeks, the first weeks of the year 0 ~ 51
Hour Hh H Time 0 ~ 23
Minute Mi N Minutes 0 ~ 59
Second Ss S seconds 0 ~ 59
Millisecond Ms - Millisecond 0 ~ 999

Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also available for access and ASP, and these functions are similar in usage

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

2.DateDiff (' s ', ' 2005-07-20′, ' 2005-7-25 22:56:32′) return value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20′, ' 2005-7-25 22:56:32′) return value is 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-25th number
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, 2005

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.