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

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