Date Functions

Name

Category

Arguments

Result

Description

Example

Make_Date

Date Functions

NUMBER year, NUMBER month, NUMBER day, [NUMBER hour], [NUMBER minute], [NUMBER second]

DATE

Creates a date from given year, month, day, hour, minute, and second.

Make_Date(2009, 3, 12) will create a date with value 12th March 2009. If time is not specified, then midnight time is taken as default.

Date

Date Functions

TEXT format, [DATEFIELD/TIMESTAMP t]

TEXT

Returns a text formatted according to the given format text using the given integer Timestamp or Database Date Field. Timestamp is optional and defaults to the value of current time.

The following characters are recognized in the format parameter text. (see below:)

date(“d-M-Y”, now()) returns 16-Sep-2015

Date Description:

Format Character

Description

Example returned values

Day

d

Day of the month, 2 digits with leading zeros

01 to 31

D

A textual representation of a day, three letters

Mon through Sun

j

Day of the month without leading zeros

1 to 31

l (lowercase ‘L’)

A full textual representation of the day of the week

Sunday through Saturday

N

ISO-8601 numeric representation of the day of the week (added in PHP 5.1.0)

1 (for Monday) through 7 (for Sunday)

S

English ordinal suffix for the day of the month, 2 characters

st, nd, rd orth. Works well with j

w

Numeric representation of the day of the week

0 (for Sunday) through 6 (for Saturday)

z

The day of the year (starting from 0)

0 through 365

Week

W

ISO-8601 week number of year, weeks starting on Monday (added in PHP 4.1.0)

Example: 42 (the 42nd week in the year)

Month

F

A full textual representation of a month, such as January or March

January through December

m

Numeric representation of a month, with leading zeros

01 through 12

M

A short textual representation of a month, three letters

Jan through Dec

n

Numeric representation of a month, without leading zeros

1 through 12

t

Number of days in the given month

28 through 31

Year

L

Whether it’s a leap year

1 if it is a leap year, 0 otherwise.

o

ISO-8601 year number. This has the same value as Y, except that if the ISO week number(W) belongs to the previous or next year, that year is used instead. (added in PHP 5.1.0)

Examples: 1999 or 2003

Y

A full numeric representation of a year, 4 digits

Examples: 1999 or 2003

y

A two digit representation of a year

Examples: 99 or 03

Time

a

Lowercase Ante meridiem and Post meridiem

am or pm

A

Uppercase Ante meridiem and Post meridiem

AM or PM

B

Swatch Internet time

000 through 999

g

12-hour format of an hour without leading zeros

1 through 12

G

24-hour format of an hour without leading zeros

0 through 23

h

12-hour format of an hour with leading zeros

01 through 12

H

24-hour format of an hour with leading zeros

00 through 23

i

Minutes with leading zeros

00 to 59

s

Seconds, with leading zeros

00 through 59

u

Microseconds (added in PHP 5.2.2). Note that date() will always generate000000 since it takes an integerparameter, whereas DateTime::format() does support microseconds if DateTime was created with microseconds.

Example: 654321

Timezone

e

Timezone identifier (added in PHP 5.1.0)

Examples: UTC, GMT, Atlantic/Azores

I (capital i)

Whether or not the date is in daylight saving time

1 if Daylight Saving Time, 0 otherwise.

O

Difference to Greenwich time (GMT) in hours

Example: +0200

P

Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3)

Example: +02:00

T

Timezone abbreviation

Examples: EST, MDT

Z

Timezone offset in seconds. The offset for timezones west of UTC is always negative, and for those east of UTC is always positive.

-43200 through 50400

Full Date/Time

c

ISO 8601 date

2004-02-12T15:19:21+00:00

r

RFC 2822 formatted date

Example: Thu, 21 Dec 2000 16:01:07 +0200

U

Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)

Name

Category

Arguments

Result

Description

Example

Now

Date Functions

NUMBER

Returns a 10 digit current timestamp.

Now() will return 1442401200 for 16-Sep-2015 11:00AM

Date_Add

Date Functions

DATEFIELD/TIMESTAMP t, NUMBER n, [TEXT t]

NUMBER

Performs arithmetic operation on date by adding years, months, weeks, days, hours, minutes, and seconds. (Please see below:)

Date_Add(input_date, 2, “d”) will add two days to input date Date_Add(Make_Date(2015, 2, 12), 10, “d”)) will return 22nd of February 2015

Date_Add Description:

Format Character

Description

Example returned values

d

days

Date_Add(input_date, 2, “d”) will add two days to input date

m

months

Date_Add(input_date, -1, “m”) will substract one month from input date

y

years

Date_Add(input_date, 1, “y”) will add one year to input date

w

weeks

Date_Add(input_date, 3, “w”) will add three weeks to input date

h

hours

Date_Add(input_date, 12, “h”) will add twelve hours to input date

min

minutes

Date_Add(input_date, -30, “min”) will substract thrity minutes from input date

s

seconds

Date_Add(input_date, 120, “h”) will add one hundred twenty seconds to input date

Name

Category

Arguments

Result

Description

Example

DateTime_Diff

Date Functions

DATEFIELD/TIMESTAMP start, DATEFIELD/TIMESTAMP end

NUMBER

This function gives the to seconds between two dates.

DateTime_Diff([Start_Date], [End_Date]) will return total seconds between Start_Date and End_Date DateTime_Diff(Make_Date(2015, 2, 12), Make_Date(2015, 2, 22)) will return 864000

Add_Months

Date Functions

DATE d, NUMBER m

DATE

Adds m months to date d and returns the date. It preserves the day of the original date. If that day is not in the new date, then last day of that month is returned.

Add_Months([Sales_Date], 2) will return a date which is two months after Sales_Date field Add_Months(Make_Date(2015, 5, 12), 2) will 12th July 2015 as date

Add_Years

Date Functions

DATE d, NUMBER y

DATE

Adds y years to date d and returns the date. It preserves the day of the original date. If that day is not in the new month of the new date, then last day of that month is returned.

Adjust_Years([Sales_Date], 2) will return a date which is two years after Sales_Date field Adjust_Years(Make_Date(2014, 1, 14), 2) will 14th January 2016 as date

Day

Date Functions

DATE d

NUMBER

Returns the day of the month of the Date d.

Day(Make_Date(2015, 3, 28)) will 28

Day_Of_Week

Date Functions

DATE d

NUMBER

Returns the number of days by which the given date d follows the first day of the week (Sunday returns 0).

Examples: Day_Of_Week(Make_Date(2015, 9, 26)) returns 6 (Saturday) Day_Of_Week(Make_Date(2015, 1, 30)) returns 5 (Friday)

Day_Of_Year

Date Functions

DATE d

NUMBER

Returns the number of days by which the given date d follows the first day of the year (January 1 returns 0).

Day_Of_Year(Make_Date(2015, 9, 29)) returns 271 Day_Of_Year(Make_Date(2015, 12, 9)) returns 342

First_Day_Of_Month

Date Functions

DATE d

DATE

Returns the first day of the month in which the date falls.

First_Day_Of_Month(Make_Date(2015, 10, 13)) 1st October 2015 as Date

First_Day_Of_Year

Date Functions

DATE d

DATE

Returns the first day of the year in which the date falls.

First_Day_Of_Year(Make_Date(2015, 7, 17)) 1st July 2015 as Date

First_Day_Of_Week

Date Functions

DATE d

DATE

Returns the first day (Sunday) of the week in which the date falls.

First_Day_Of_Week(Make_Date(2015, 5, 9)) will return 3rd May 2015 as Date

Is_Leap_Day

Date Functions

DATE d

BOOLEAN

Returns true if Date d is 29th of February.

Is_Leap_Day(Make_Date(2015, 2, 29)) will return false Is_Leap_Day(Make_Date(2016, 2, 29)) will return true

Is_Leap_Year

Date Functions

DATE d

BOOLEAN

Returns true if the Date d falls in a leap year.

Is_Leap_Year(Make_Date(2015, 2, 29)) will return false Is_Leap_Year(Make_Date(2016, 2, 29)) will return true

Working_Days_In_A_Month

Date Functions

DATE d, NUMBER weekend_setting, TEXT holidays

NUMBER

Weekend Settings:

1: Saturday, Sunday

2: Sunday

3: Friday, Saturday

Holidays: Comma separated timestamps

working_days_in_a_month(Make_Date(2016, 3, 1, 0, 0, 0), 1,””) return 23

working_days_in_a_month(Make_Date(2016, 3, 1, 0, 0, 0), 1, Make_Date(2016, 3, 23, 0, 0, 0)) return 22

Last updated