Date Functions:
DATE() :
This function returns Date in the
date format. These dates are interpreted according to the system date. It supports date after 1st march
1900.
Syntax:
DATE(<Year>,<Month>,<Day>)
Parameters:
Year :
-
It should be a number.
-
Anything more than 9999 or a negative value,
system will throw an error.
-
The value can be of 1 to 4 digited value.
-
If we are passing a single digit as the first
argument, it will add that number to 1900 and returns the date.
o
For example if we pass 05 as argument, it will
give 1905 as the year. DATE(05,10,15) will give 15th October 1905.
-
If we pass a decimal value, it will round off
the value and then calculates the year. If we pass any value between 0 and
1899, it will add it to 1900 and will give that as an year. For example –
o
DATE(1500,1,10) returns 1st October
3400 (1900 + 1500)
o
DATE(2016,10,10) returns 10th October
2016.
Month :
-
It should be a number.
-
If the value is between 1 and 12, system will
consider them as the months of the year. 1 represents January, 2 – February,
and so on till 12 – December.
-
If the value is more than 12, it will compute
the date by adding it to the month of the year. For example – DATE(2016,15,01)
returns 01st March 2017. It adds number of months (15) will be added
to 2016 from January.
-
If we value is -6, it will add -6 months to the
given year. DATE(2016,-6,1) will return
1st January 2015.
Day :
-
It should be a number.
-
If the value is between 1 and the last day of
the month, it will treat that as the day of the month. Else will compute the
date by adding the given number of days to the 1st of the given
month. For example –
o
DATE(2016,04,10) will return 10th April
2016.
o
DATE(2016,04,40) will return 10th May
2016.
o
DATE(2016,04,-10) will return 21th
March 2016.
DAY():
Returns the Day of
the month ranging from 1 to 31.
Syntax:
DAY(<Date>
Parameter :
<Date> : should be a valid
date. It can be a text in valid date format.
o
If the system date format is ‘dd/mm/yyyy’, DAY(“01/10/2016”)
will be interpreted as 1st October 2016 and it returns 1.
o
If the system date format is ‘mm/dd/yyyy’, DAY(“01/10/2016”) will be
interpreted as 10th January 2016 and it returns 10.
MONTH():
Returns the Month
number of the given Date.
Syntax :
MONTH(<Datetime>
Parameter:
<Datetime> : should be in
valid datetime format. It can be in text in valid datetime format.
o
If the system date format is ‘dd/mm/yyyy’, DAY(“01/10/2016”)
will be interpreted as 1st October 2016 and it returns 10.
o
If the system date format is ‘mm/dd/yyyy’, DAY(“01/10/2016”) will be
interpreted as 10th January 2016 and it returns 1.
YEAR():
Returns the year of
the given date ranging from 1900 till 9999.
Syntax:
YEAR(<Datetime>)
Parameter:
<Datetime> should be in
valid datetime format. It can be in text in valid datetime format.
For Example:
YEAR(“01-Jan-2017”) returns
2017.
HOUR():
Returns the hour as
number from 0 to 23 which corresponds to 24 hour clock.
Syntax:
HOUR(<Datetime>)
Parameter:
<Datetime>
should be in valid datetime format. It can be in text in valid datetime format.
For Example:
HOUR(“January 10, 2017 03:30:30
PM”) returns 15.
HOUR(“January 10, 2017 03:30:30 AM”)
returns 3.
MINUTE():
Returns the Minute as
number from 0 to 59.
Syntax:
MINUTE(<Datetime>)
Parameter:
<Datetime> should be in
valid datetime format. It can be in text in valid datetime format.
For Example:
MINUTE(“January 10, 2017 03:30:30
PM”) returns 30.
SECOND:
Returns the Second number from 0
to 23 which corresponds to 24 hour clock.
Syntax:
SECOND(<Datetime>)
Parameter:
<Datetime> should be in
valid datetime format. It can be in text in valid datetime format.
For Example:
SECOND(“January 10, 2017 03:30:30
PM”) returns 30.
NOW():
Returns the current date and time in datetime format. It gives the exact time
when it was called. It won’t be updated continuously.
o
We can use NOW() function in calculations.
o
If we use NOW()+4 it will add four days to the
current date.
TODAY(): Returns current date and time in datetime format.
It gives the current date with 12:00:00AM as output.
-
We can use TODAY() function in calculations.
-
If we use TODAY()+10 it will add 10 days to the
current date.
WEEKDAY(): Returns a number which is
the day of week. The number ranges from 1 to 7. By default the day ranges from
1 – Sunday to 7- Saturday.
Syntax:
WEEKDAY(<Date>,<Return_Type>)
Parameters:
Date:
should be in valid datetime format. It can be in text in valid datetime format.
Return_type:
It’s a number that determines the return type. Return types are as follows -
§
1- Week Begins on Sunday (1) and ends on
Saturday (7)
§
2- Week Begins on Monday(1) and ends on
Sunday(7)
§
3- Week begins on Monday(0) and ends on
Sunday(6)
If we omit passing the second argument,
by default it will take 1 as return type.
WEEKNUM(): Returns the week number of
the year according to the return type.
Syntax:
WEEKNUM(<Date>,<Return_Type>)
Parameters:
Date: should be in valid datetime format. It can be in text in valid datetime format.
Return_Type: It’s a number that determines the return type. Default is 1. Return types are as follows -
§ 1- Week Begins on Sunday
§ 2- Week Begins on Monday
Please give comments.
Comments are appreciated.
