SQL Server DATEPART() for date and time values
DATEPART() returns an integer for a requested component of a SQL Server date/time value. It is useful when a report needs the month, year, day of year, hour or another individual part.
Get the month
SELECT DATEPART(month, issue_dt) AS issue_month
FROM dbo.dt;The result is an integer from 1 through 12.
Get the day of the year
SELECT DATEPART(dayofyear, issue_dt) AS day_of_year
FROM dbo.dt;dayofyear normally returns 1 through 365, or 366 in a leap year.
Useful DATEPART names
| Datepart | Common abbreviation | Meaning |
|---|---|---|
| year | yy, yyyy | Year |
| quarter | qq, q | Quarter |
| month | mm, m | Month |
| dayofyear | dy, y | Day of year |
| day | dd, d | Day of month |
| week | wk, ww | Week |
| weekday | dw | Weekday |
| hour | hh | Hour |
| minute | mi, n | Minute |
| second | ss, s | Second |
| millisecond | ms | Millisecond |
| iso_week | isowk, isoww | ISO week number |
Weekday note: the numeric result for
DATEPART(weekday,...) is affected by SQL Server's SET DATEFIRST setting. Do not assume a fixed weekday number without controlling that setting.Use DATEPART() from Classic ASP
<%
Dim rs1
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open "SELECT DATEPART(dayofyear, return_dt) AS day_no FROM dbo.dt ORDER BY issue_id", conn
Do While Not rs1.EOF
Response.Write Server.HTMLEncode(CStr(rs1("day_no"))) & "<br>"
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
%>ASP Home
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Connect:
