How to get the Month, Year or Day from a DateTime field in SQL

If you have a datetime field in SQL, it will usually be formatted as "dd/MM/yyyy hh:mm:ss" (assuming you're using European format, if American, it'll be MM/dd/yyyy). If you want to get the date part, its easy, just do :-

Print cast(dateField as date)

To get the individual parts, you could try to be clever and by converting the date field in to a string and then splitting it. To be wholey unoriginal, it would be a sledgehammer to crack a nut. There is an easier way, if there wasn't, would I be writing this article?

To get the individual parts of a datetime field, you would use the DatePart built-in function. See the example below to see how it is done.

Print datePart(month,dateField)

month is not an enumeration that month is code for, month is the actual parameter that you need to pass in. DatePart is not limited to time, you can use the following parameters to get what you require.

• year, yyyy, yy = Year
• quarter, qq, q = Quarter
• month, mm, m = month
• dayofyear, dy, y = Day of the year
• day, dd, d = Day of the month
• week, ww, wk = Week
• weekday, dw, w = Weekday
• hour, hh = hour
• minute, mi, n = Minute
• second, ss, s = Second
• millisecond, ms = Millisecond
• microsecond, mcs = Microsecond
• nanosecond, ns = Nanosecond
• tzoffset, tz = Timezone offset
• iso_week, isowk, isoww = ISO week

Tags - SQL

Last Modified : June 2023