Example and Notes
Use this SQL Server example as a starting point for development, troubleshooting, reporting, or maintenance work. Review it against your schema, data volume, permissions, and SQL Server version before using it in production.
The DatePart function is a powerful SQL function you can use to manipulate and work with dates and return an integer value based on the part of the date you select. For example, you may want to get the number of days, hours, minutes, etc. in a specific date. The example scripts below will show you how to get specific intervals for a date.
Examples of Using the DatePart Function
DECLARE @Date datetime2 = '2018-09-25 19:47:00.8631597'
|
| Unit of time |
Abbreviations |
Query |
Result |
| ISO_WEEK |
isowk, isoww |
SELECT DATEPART(ISO_WEEK,@Date) |
38
|
| TZoffset |
tz |
SELECT DATEPART(TZoffset,@Date) |
0
|
| NANOSECOND |
ns |
SELECT DATEPART(NANOSECOND,@Date) |
863159700
|
| MICROSECOND |
mcs |
SELECT DATEPART(MICROSECOND,@Date) |
863159
|
| MILLISECOND |
ms |
SELECT DATEPART(MS,@Date) |
863
|
| SECOND |
ss, s |
SELECT DATEPART(SS,@Date) |
0
|
| MINUTE |
mi, n |
SELECT DATEPART(MINUTE,@Date) |
47
|
| HOUR |
hh |
SELECT DATEPART(HH,@Date) |
19
|
| WEEKDAY |
dw |
SELECT DATEPART(DW,@Date) |
1
|
| WEEK |
wk, ww |
SELECT DATEPART(WEEK,@Date) |
40
|
| DAY |
dd, d |
SELECT DATEPART(DAY,@Date) |
25
|
| DAYOFYEAR |
dy, y |
SELECT DATEPART(DAYOFYEAR,@Date) |
268
|
| MONTH |
mm, m |
SELECT DATEPART(MM,@Date) |
9
|
| QUARTER |
qq, q |
SELECT DATEPART(QUARTER,@Date) |
3
|
| YEAR |
yy, yyyy |
SELECT DATEPART(YYYY,@Date) |
2018 |
|
Production Review
WSI can adapt this script for your database, improve error handling, tune performance, document the logic, and help deploy it safely.