SQL Server DATEPART() for date and time values
Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.
Extract a month with DATEPART()
SQL example
SELECT DATEPART(month, issue_dt) AS issue_month
FROM dbo.dt
ORDER BY issue_dt;Use DATEPART when you need a particular unit such as month, dayofyear, or quarter.
On this page — Show page sections
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 |
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
%>Week and weekday settings
DATEPART(weekday, ...) depends on SET DATEFIRST, and week numbering can differ from ISO weeks. Use DATEPART(isowk, datevalue) when ISO 8601 week numbers are required.
SQL and VBScript functions differ
VBScript DatePart() runs inside ASP, while SQL Server DATEPART() runs inside a query. Choose the layer where the date value is actually being processed.
Frequently asked questions
- Are these queries for SQL Server or Access?
- The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.
- Can I execute these statements directly on a live database?
- Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.
- How should Classic ASP send input to a query?
- Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.
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.
