Query Tip: How to Break Down a Date into Separate Fields (DATE PART, DATENAME)

 Note: This is not a comprehensive description of the capabilities of either of these SQL Functions.

Suppose you would like to get the current month number for a date in SAP Business One. Let’s use the Document Date from the Purchase Order table (OPOR.DocDate).

We will be looking at the DATEPART and DATENAME Functions in SQL. DATEPART will return a integer (number) and DATENAME will return a character (e.g., “November”).

Use either of these functions when selecting fields from the database.
For Example:

SELECT
DocDate /* The actual date from the PO */
,DatePart(mm,DocDate) AS Month_Number /* a Number(1-12) */
,DateName(mm,DocDate) AS Month_Name /* a Name like “November” */
FROM OPOR /* Purchase Order Table*/

Note the “mm” in the DATEPART and DATENAME lines. This tells the DBMS that you would like to look at the month of the Document Date from the Purchase Order (OPOR). There are other codes that you could use in the place of “mm”. If you would like to extract the year, use “yy” or the day of the month, “dd”. There are other codes for day of the week, both in name and in number.