DAYOFMONTH

Introduction

The DAY function is used to extract and return the day of the month from a given date expression (date or timestamp_ltz type), with the result being an integer.

Syntax

dayofmonth(expr)

Parameters

• expr: The date or time expression from which the days need to be extracted. It can be in formats such as string, timestamp, etc.

Return Result

Returns an integer representing the number of days in the month of the input expression.

Usage Example

1. Extract days from a string type date:

SELECT dayofmonth('2022-03-31'); -- Result: 31

2. Extract the number of days from the current timestamp:

SELECT dayofmonth(TIMESTAMP_LTZ '2022-08-15 03:21:00'); -- Result: 15

3. Extract days from a string-type timestamp:

SELECT dayofmonth('2022-09-01 10:30:20'); -- Result: 1

4. Extract and add the prefix "000" from the current timestamp:

SELECT '000' || dayofmonth(TIMESTAMP_LTZ '2022-10-20 14:45:30'); -- Result: 00020

Notes

• Ensure that the input date or time expression format is correct, otherwise, the function may not execute properly.
• When handling string-type time expressions, please ensure that the string format matches the format supported by the database.

By using the DAY function, you can easily extract the day from different types of date expressions for further date calculations or data display.