EXTRACT( ) returns a variety of elements extracted by decoding fields of date/time types. It can operate on all dialect 3 and dialect 1 date/time fields.
Here’s the syntax for EXTRACT( ):
element must be a defined element that is valid for the data type of field. Not all elements are valid for all date/time types. The data type of element varies according to the element extracted. Table enumerates the elements available for each date/ time type.
field can be a column, a variable, or an expression that evaluates to a date/time field.
Table shows the restrictions on the arguments and types when using EXTRACT( ).
EXTRACT( ) Arguments, Types, and Restrictions
Combining EXTRACT( ) with Other Functions
Following are two examples where EXTRACT( ) is used with CAST( ) to obtain date representations not available with either function on its own.
To Cast Date Plus Time Without Sub-Seconds
Although it is not possible by direct casting to return a cast date plus time string without the sub-second portion, it can be done using a complex expression involving both CAST( ) and EXTRACT( ).
To Extract a TIME String
This technique has more meaning for dialect 1 than for dialect 3. However, it can be extrapolated to any dialect 3 date or time type if you need to store time of day as a string.
The EXTRACT( ) function makes it possible to extract the individual elements of date and time types to SMALLINT values. The following trigger extracts the time elements from a dialect 1 DATE column named CAPTURE_DATE and converts them into a CHAR(13), mimicking the Firebird standard time literal 'HH:NN:SS.nnnn':
A Sample Date/Time Type Conversion Task
The CHAR(13) string stored by the trigger in the preceding example does not behave like a dialect 3 TIME type. However, by simple casting, it can be converted directly, in a later upgrade to dialect 3, to a dialect 3 TIME type.
First, we add a temporary new column to the table to store the converted time string:Next, populate the temporary column by casting the dialect 1 time string:
The next thing we need to do is temporarily alter our trigger to remove the reference to the dialect 1 time string. This is needed to prevent dependency problems when we want to change and alter the old time string:
Now, we can drop the old CAPTURE_TIME column:
Create it again, this time as a TIME type:
Move the data from the temporary column into the newly added CAPTURE_TIME:
Drop the temporary column:
Finally, fix up the trigger so that it now writes the CAPTURE_TIME value as a TIME type:
All of these steps can be written as an SQL script. For details about SQL scripting, refer to the topic “Schema Scripts” in Chapter From Drawing Board to Database.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.