CODEWITHSUNDEEP
sqlsql-servert-sql
Lionel Kirk
Lionel Kirk

Reputation: 19

SELECT DATEPART, DATENAME or other? SQL

I am creating a new query where I would like to add three additional columns (same format):

Week Number (as 08) / Month (as February) / Year (as 2019)

SELECT hd.F91 AS [PO Number],
hd.F1032 AS [Trs Number],
hd.F76 AS [Order Date],
hd.F27 AS [Vendor ID],
hd.F334 AS [Vendor Name],
hd.F1127 AS Admin,
hd.F1068 AS State,
hd.F1067 AS Status,
tl.F65 AS Total,
DATEPART(wk,[Order Date]) AS [Week Number],
DATENAME('month',[Order Date]) AS Month,
DATENAME('year',[Order Date]) AS Year,

The rest of the data is being pulled from our system but I tried to get these three columns based on an already existing column called Order Date (date format).

Unfortunately, the error below showed up. All suggestions to try would be welcome. Thanks!

enter image description here

Upvotes: 0

Views: 875

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

THIS QUESTION WAS ORIGINALLY TAGGED FOR POSTGRES.

The ANSI standard functionality is:

extract(week from "Order Date")
extract(month from "Order Date")
extract(year from "Order Date")

Postgres supports this standard syntax.

If you want a string, you can use to_char():

to_char("Order Date", 'WW')
to_char("Order Date", 'YYYY')
to_char("Order Date", 'Month')

Upvotes: 0

forpas
forpas

Reputation: 164139

These functions work in mysql (as was the tag of the question before edited!!!):

WEEK([Order Date], 1) AS [Week Number],
MONTHNAME([Order Date]) AS Month,
YEAR([Order Date]) AS Year,

change the argument 1 in WEEK according to your needs
(https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week)

Upvotes: 1

Wired604
Wired604

Reputation: 370

try this it worked for me in postgreSQL to_char([order date], 'Month')

Upvotes: 0

Related Questions