You are here: Reference Information > SQL Reference Information > SQL Functions and Operations > SQL Data Type Conversion Functions

SQL Data Type Conversion Functions

Date to/from String Conversion

NuoDB supports functions for converting date/time data types to formatted strings and for converting from formatted strings to date/time data types. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format. If either argument is NULL, the function will return NULL.

Syntax

In the following table, the timestamp parameter is used to represent any DATE, TIME or TIMESTAMP.

Function Syntax Return Type Description
DATE_TO_STR ( timestamp, pattern ) STRING Convert timestamp to a STRING data type based on the pattern specified
DATE_FROM_STR ( string, pattern ) DATE | TIME | TIMESTAMP depending on the pattern specified Convert string to a TIMESTAMP data type in the pattern specified

For the DATE_TO_STR and DATE_FROM_STR functions, there are certain patterns that are recognized and replaced with appropriately formatted data from the value to be formatted. Any portion of the value that is not a template pattern can be quoted using single quotes and is simply copied verbatim. Case is significant. NuoDB supports the following date and time pattern strings.

Template Patterns for Date / Time Formatting

Pattern Description Examples
H hour of the day (00:23) 01, 13
m minute (00-59) 30
s second (00-59) 50
S fractions of a second up to nanoseconds. On output, it will define the field size with padding (000000000-999999999) 123
a meridian indicator a
y year 2013, 13
M month number (01-12) June, Jun, 06
d day of month (0-31) (depending on month) 10
D day of year (365) (+1 for Leap Year) 365
w week of year (01-52) 2
Z time zone -0500 (EST)

Examples of DATE_TO_STR conversion

SELECT DATE_TO_STR(now(), 'MM-dd-YYYY HH:mm:ss.SSSSSS')
  FROM DUAL;
           date_to_str           
 ---------------------------------
 01-31-2013 12:13:14.123456
 
SELECT DATE_TO_STR(now(), 'yyyy-MM-dd''T''HH:mm:ss.SSSSSSSSS ZZZZZ')
  FROM DUAL;
             date_to_str             
 ------------------------------------
 2013-01-31T12:13:14.123456000 -05:00
 
SELECT DATE_TO_STR(now(), 'yyyy.MM.dd'' at ''HH:mm:ss Z')
  FROM DUAL;
       date_from_str       
 ----------------------------
 2013.01.31 at 12:13:14 -0500
 
SELECT DATE_TO_STR(now(), 'MMM d, yyyy')
  FROM DUAL;
 date_to_str 
 ------------
 Jan 31, 2013
 
SELECT DATE_TO_STR(now(),'h:mm a z') FROM DUAL;
 date_to_str 
 --------------
   12:13 PM EST 
 
SELECT DATE_TO_STR(now(),'yyyyy.MMM.dd hh:mm a') FROM DUAL;
      date_to_str     
 ---------------------
 02013.Jan.31 12:13 PM
 
SELECT DATE_TO_STR(now(),'d MMM yyyy HH:mm:ss Z') FROM DUAL;
        date_to_str       
 -------------------------
 31 Jan 2013 12:13:14 -0500 
 
SELECT DATE_TO_STR(CURRENT_DATE,'MM/dd/yyyy') FROM DUAL;
 date_to_str 
 ------------
  01/31/2015

Examples of DATE_FROM_STR conversion

SELECT DATE_FROM_STR('01-31-2013 12:13:14.123456', 'MM-dd-yyyy HH:mm:ss.SSSSSS')
  FROM DUAL;
       date_from_str       
 --------------------------
 2013-01-31 12:13:14.123456
 
SELECT  DATE_FROM_STR('2013-01-31T12:13:14.123456 +00:00', 'yyyy-MM-dd''T''HH:mm:ss.SSSSSSSSS ZZZZZ')
  FROM DUAL;
/* This is being run from server set at Eastern Standard time zone */
/* The timestamp result is converted to the local time zone */
      date_from_str   
--------------------------
2013-01-31 07:13:14.123456 
 
SELECT DATE_TO_STR(DATE_FROM_STR( '2013-01-31 12:13:14.123456 +0100',
  'yyyy-MM-dd HH:mm:ss.SSSSSSSSS ZZZZ'),
  'yyyy-MM-dd''T''HH:mm:ss.SSSSSSSSS ZZZZ')
  FROM DUAL;
           date_from_str   
---------------------------------------
2013-01-31T06:13:14.123456000 GMT-05:00

Data Type Conversion

NuoDB supports conversion from one data type to another as shown in the table below. See also:

Syntax

Function Syntax Return Type Description
CAST ( expression AS type [length] ) Data Type specified by type Convert expression to a data type of type specified. The length argument can be provided if the data type specified by the type argument supports it. In the case of decimal or numeric data type, length can be: precision[,scale]

Examples of CAST to convert the data type

/* converting decimal to integer will round */
SELECT CAST(0.5 AS INTEGER) FROM DUAL;
 - 
 1
 
/* convert timestamp to Unix time or POSIX time */
SELECT CAST(DATE('2014-01-01 06:12:34.123456789') AS DECIMAL) FROM DUAL;
---------- 
 1388534400
 
/*  Math operations with data type integer yields integer results */
SELECT (-1 * 3/6) FROM DUAL;
 -
 0 


/* By casting the integer to a non-integer data type, the result will return 
   non-integer */
SELECT CAST(-1 AS FLOAT) * 3/6 FROM DUAL;
 ----
 -0.5


SELECT CAST(-1 AS DECIMAL) * 0.5 FROM DUAL;
 ----
 -.5