DatePart constants to collect parts of Date & time field of MSSQL

DatePart in MSSQL date & time field

We can collect a particular date part from any date and time field of MSSQL table. We have to specify which part of the date and time is required. You can get a list of constants used for formatting DatePart function.

Getting Month using DatePart function

We have one date and time field as issue_dt and we want to know the month of this date and time data.

DatePart(month,issue_dt)

We will get month between 1 to 12 here.

Getting the Day of the year

DatePart(dy,issue_dt)

Here we will get the value from 1 to 365 ( days of the year )

Same way we can use the table below and get all the parts from a date and time field of MSSQL table.

Constant	Meaning
ms	Millisecond
ss	second
mi	Minute
hh	Hour
dd	Day
dy	Day of Year
dw	Weekday
wk	Week
mm	Month
qq	Quarter
yy	Year

Let us try this inside one sql query and get the data

rs1.open " select DatePart(dy,return_dt) as dp from dt " , conn

Do While Not rs1.EOF
Response.Write rs1("dp")
Response.Write "<br>"
rs1.MoveNext
Loop

This article is written by plus2net.com team.

Be the first to post comment on this article :

plus2net.com

MSSQL Date & Time function Examples

Inserting present date and time in a field by default

How to format Date & Time variable to insert into MSSQL table

DatePart to get part of a date & time field of MSSQL record

Updating MSSQL date & time field

DateDiff: Getting the Difference in days, month and year between two date fields

Month: Month in numbers from DateTime field

Year: Year from DateTime field

Day: Day from DateTime field

Example of Date Query: Last n days record, todays records