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

Introduction

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