SQL Server date function set dateadd, datediff, datename, datepart

Source: Internet
Author: User
Tags month name

The date and time functions in SQL Server are always problematic in this regard. So, I will summarize what I mainly useDatepart(Month, getdate (). This item can be set to day (now () in access. Unfortunately, the day () in SQL Server does not support parameters.

Date and time functions in SQL Server

These scalar functions operate on the input values of date and time, and return a string, numeric value, or Date and Time Value.

The following table lists Date and Time Functions and Their deterministic attributes.

Function Certainty
Dateadd Deterministic
Datediff Deterministic
Datename Not deterministic
Datepart All functions except datepart (DW, date) are deterministic. DW is the date part of the working day, depending on the value set by set datefirst on the first day of each week.
Day Deterministic
Getdate Not deterministic
Getutcdate Not deterministic
Month Deterministic
Year Deterministic
Deterministic and non-deterministic Functions

All functions are deterministic or non-deterministic:

  • When using a specific input value set to call a deterministic function, they always return the same result.
  • Different results may be returned each time a non-deterministic function is called using a specific input value set.

Whether a function is a deterministic function or a non-deterministic function is called a function's certainty.

For example, the dateadd built-in function is a deterministic function, because the same result is always returned for any given parameter value set of the three parameters. Getdate is not a deterministic function, because it is always called with the same parameters, and it returns different results each time it is executed.

 

 

Print outPubsA list of the time structures of titles in the database. This time structure indicates the current release date plus 21 days.

USE pubsGOSELECT DATEADD(day, 21, pubdate) AS timeframeFROM titlesGO
  

Are you sure you wantPubsThe title publication date in the database and the number of days in the current day.

USE pubsGOSELECT DATEDIFF(day, pubdate, getdate()) AS no_of_daysFROM titlesGO

    
 

Extract the month name from the date returned by getdate.

SELECT DATENAME(month, getdate()) AS 'Month Name'

      
 
The getdate function returns the current date. However, the comparison does not always require complete date information (usually only a part of the date is compared ).
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
      

      
      
Date Abbreviations
Year YY, yyyy
Quarter QQ, Q
Month Mm, m
Dayofyear Dy, y
Day DD, d
Week Wk, WW
Weekday DW
Hour HH
Minute Mi, n
Second SS, S
Millisecond MS
 
The above table may be too messy if you have many languages,
Different languages are different in some places, but I don't know if you have found out,
No matter what language,
They can all use the full name directly without the need to use the short name. I like it very much. I will use the full name directly if I don't write it.
Original Author:Liu yongfa (yongfa365) 'blog

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.