Interbase : How to get the weekday , month, year and day from timestamp

its amazing that a lot of the functions needed in our daily lives get embedded to a lot of software’s out there.

This is a case that i needed to generate employees who went in on Monday. I was able to dig out these great finds.

Enjoy

Coffee Cup

 

The data type of EXTRACT() expressions depends on the specific part being extracted:

Extract	Resulting data type	Representing
YEAR	SMALLINT	Year, range 0-5400
MONTH	SMALLINT	Month, range 1-12
DAY	SMALLINT	Day, range 1-31
HOUR	SMALLINT	Hour, range 1-23
MINUTE	SMALLINT	Minute, range 1-59
SECOND	DECIMAL(6,4)	Second, range 0-59.9999
WEEKDAY	SMALLINT	Day of the week, range 0-6 (0 = Sunday, 1 = Monday, and so on)
YEARDAY	SMALLINT	Day of the year, range 1-366

SELECT EXTRACT (YEAR FROM timestamp_fld) FROM table_name;
=======
1999
SELECT EXTRACT (YEAR FROM timestamp_fld) FROM table_name;
=======
1999
SELECT EXTRACT (MONTH FROM timestamp_fld) FROM table_name;
=======
6
SELECT EXTRACT (DAY FROM timestamp_fld) FROM table_name;
=======
25
SELECT EXTRACT (MINUTE FROM timestamp_fld) FROM table_name;
=======
24
SELECT EXTRACT (SECOND FROM timestamp_fld) FROM table_name;
============
35.0000
SELECT EXTRACT (WEEKDAY FROM timestamp_fld) FROM table_name;
=======
5
SELECT EXTRACT (YEARDAY FROM timestamp_fld) FROM table_name;
=======
175
SELECT EXTRACT (MONTH FROM timestamp_fld) ||
'-' || EXTRACT (DAY FROM timestamp_fld) ||
'-' || EXTRACT (YEAR FROM timestamp_fld) FROM table_name;
====================
6-25-1999

Source : http://docwiki.embarcadero.com/InterBase/XE7/en/Extracting_Date_and_Time_Information