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, TIMESTAMP or TIMESTAMP WITHOUT TIME ZONE.

Function Syntax Description

DATE_TO_STR ( timestamp, pattern )

Convert timestamp to a STRING data type based on the pattern specified.

Return type: STRING

DATE_FROM_STR ( string, pattern )

Convert string to a TIMESTAMP data type in the pattern specified.

Return type: TIMESTAMP

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 microseconds. On output, it will define the field size with padding (000000-999999)

123

a

meridian indicator

a

y

year

2013, 13

Y

year in ISO week date system

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

E

day of week name

Tue, Tuesday

e

day of week numeric/name

2, Tue, Tuesday

Z

time zone

-0500 (EST)

Read Unicode Date Format Patterns documentation for a complete list of symbols and other details.

Y year symbol is meant to be used together with week of year and day of week. NuoDB reports an error if the date format pattern contains Y and also includes either month or day of month symbols.

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.SSSSSS ZZZZZ')
  FROM DUAL;
            [DATE_TO_STR]
 ------------------------------------
 2013-01-31T12:13:14.123456 -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.SSSSSS ZZZZZ')
  FROM DUAL;
     [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.SSSSSS ZZZZ'),
  'yyyy-MM-dd''T''HH:mm:ss.SSSSSS ZZZZ')
  FROM DUAL;
          [DATE_FROM_STR]
---------------------------------------
2013-01-31T06:13:14.123456 GMT-05:00

Data Type Conversion

NuoDB supports conversion from one data type to another using CAST as shown in the table below.

Syntax

Function Syntax Description

CAST ( expression AS type [length] )

Converts expression to a data type of type.

  • When casting to a CHAR or VARCHAR type, the source value is truncated to the length specified for the type.

  • When casting to a BINARY or BINARY VARYING, an error is returned if the binary representation of the source value is greater than the length specified for the type.

  • When casting to a NUMERIC or DECIMAL type, the source value is rounded to the nearest value that would fit into that type. If the value cannot be rounded to fit the specified precision and scale, an error is returned.

The precision specified for TIMESTAMP is ignored.

See also:

  • CHAR() function for conversion from an integer to a UTF-8 encoded unicode character.

  • UNICODE() function for conversion of the first character in a string to an integer.

Examples of using CAST to convert the data type

Note that, in these examples, the returned column has the same name as the expression that created it (in square brackets) unless you explicitly specify a name using the AS qualifier.

SELECT CAST(0.5 AS INTEGER) FROM DUAL;
 [1]
 ----
  1

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

SELECT CAST (1234 as VARCHAR(2)), CAST(1234 as char(3)) FROM DUAL;
 ['12']  ['123']
 ------- --------
   12      123

SELECT CAST (9.999 as numeric(3,2)) FROM DUAL;
Error 22000: Numeric overflow: 10 does not fit in precision, scale(3,2)

SELECT CAST ('2010-01-01 01:01:01.010101' as TIMESTAMP(1)) FROM DUAL;
 [2010-01-01 01:01:01.010101]
 -----------------------------
  2010-01-01 01:01:01.010101

SELECT cast (NEXT VALUE FOR hibernate_sequence AS SMALLINT) AS Counter FROM DUAL;
 COUNTER
 --------
    10