Table of Contents

Search

  1. About the Data Vault SQL Reference
  2. Introduction to SQL Reference for Informatica Data Vault
  3. Date and Time Arithmetic
  4. WHERE Clauses
  5. UNION Operator
  6. Parameterized Query
  7. Functions

Data Vault SQL Reference

Data Vault SQL Reference

DATE

DATE

A cast function that converts a properly formatted input expression to the DATE datatype and returns the value. The input expression can evaluate to the CHAR, VARCHAR, TIMESTAMP, or DATE datatype. For the character input types, the following formats are permitted:
  • 'YYYY-MM-DD'
  • 'MM/DD/YYYY'
  • 'DD.MM.YYYY'
  • Any legal format that matches the date picture (DatePic) specified in the [CLIENT] section of the nucleus.ini file.
If the input type is TIMESTAMP, the DATE portion of the value is returned. If the input type is DATE, the value is returned unchanged.
The DATE (month, day, year) form of the function constructs a DATE value from integer values.

DATE Syntax

DATE (char) DATE (timestamp) DATE (month, day, year)

DATE Example

Input
Output
'22.11.1975'
1975-11-22
CREATE TABLE emp_data (eyear CHAR(4), emonth CHAR(2), eday CHAR(2)); INSERT INTO emp_data VALUES ('1975', '11', '22'); 1 row affected CREATE TABLE birthdates (d1 DATE); INSERT INTO birthdates SELECT DATE(eday || '.' || emonth || '.' || eyear) FROM emp_data; 1 row affected SELECT d1 FROM birthdates; 1 row selected d1 ---------- 1975-11-22

0 COMMENTS

We’d like to hear from you!