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