HPlogo ALLBASE/SQL Reference Manual: HP 3000 MPE/iX Computer Systems > Chapter 8 Expressions

Date/Time Functions
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

The following text describes the two types of date/time conversion functions:

  • The input functions convert character values into date/time values. With TO_DATE, TO_TIME, TO_DATETIME, and TO_INTERVAL you can enter date/time values in a format other than the default format.

  • The output functions convert date/time values out to integer or character values. With TO_CHAR you can specify an output format for a date/time column value other than the default format. With TO_INTEGER you can extract an element as an INTEGER value.

Date/time columns are displayed in the default format.

Scope

SQL Data Manipulation Statements

SQL Syntax--Conversion Functions

{ { TO_DATE TO_TIME TO_DATETIME TO_INTERVAL } (StringExpression [,FormatSpecification]) TO_CHAR (DateTimeExpression [,FormatSpecification]) TO_INTEGER (DateTimeExpression ,FormatSpecification) }

Parameters--Conversion Functions

TO_DATE, TO_TIME, TO_DATETIME, TO_INTERVAL

produce a result which is of the DATE, TIME, DATETIME, or INTERVAL type, respectively. Use these functions in any expression.

TO_CHAR

produces the character string representation of the value in the column named in the first parameter in the format specified in the second parameter. The result type is VARCHAR with the length as specified by the format specification. If a format is not specified, the default format for the data type (and length) is used. Use this output function in any expression.

TO_INTEGER

produces an INTEGER value which represents a portion of the date/time column. The format specification is not optional in this case, and must consist of a single element (of the format specification). Use this output function in any expression.

StringExpression

is a string expression. Refer to the "Expression" section in this chapter for details on the syntax. The expression must be a CHAR or VARCHAR data type.

DateTimeExpression

is a Date/Time expression. See the "Expression" section of this chapter for more details on the syntax. The expression must be a DATE, TIME, DATETIME, or INTERVAL data type.

FormatSpecification

specifies the format of ColumnName or CharacterValue. Refer to the syntax for FormatSpecification later in this section. Format elements are presented in the "Description" section below.

SQL Syntax--FormatSpecification

{ `FormatString' :HostVariable [ [INDICATOR]:IndicatorVariable] ? :LocalVariable :ProcedureParameter ::Built-inVariable }

Parameters--FormatSpecification

FormatString

is a character string literal representing the format of DateTimeExpression or StringExpression. It must be a string literal, of maximum length 72 NATIVE-3000 characters. Format is composed of one or more elements. Available format elements for the date/time data types are described below. Only NATIVE-3000 characters are allowed in the FormatString. The syntax for the format string follows:

{FormatElement {Punctuation or Blank} [...] }

The format elements are listed in the "Description" section.

HostVariable

identifies a host variable that contains the format specification which determines how the DateTimeExpression or StringExpression is to be converted.

IndicatorVariable

names an indicator variable, whose value determines whether the associated host variable contains a NULL value:

> = 0

the value is not NULL

< 0

the value is NULL (The value in the host variable will be ignored.)

?

is a place holder for a dynamic parameter in a prepared SQL statement in an application program. The value of the dynamic parameter is supplied at run time.

LocalVariable

contains a value in a procedure.

ProcedureParameter

contains a value that is passed into or out of a procedure.

::Built-inVariable

is one of the following built-in variables used for error handling:

  • ::sqlcode

  • ::sqlerrd2

  • ::sqlwarn0

  • ::sqlwarn1

  • ::sqlwarn2

  • ::sqlwarn6

  • ::activexact

The first six of these have the same meaning that they have as fields in the SQLCA in application programs. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to the chapter "Constraints, Procedures, and Rules."

Description

  • If the format specification is optional and it is not supplied, the proper default format is used. If a date/time column or string literal appears in an expression without a conversion function, it is changed, if necessary, to the default format.

  • Date format is used by the TO_DATE function and by the TO_CHAR function on DATE expressions. The default format is 'YYYY-MM-DD'.

    Listed here are format elements made up of numeric characters (digits 0 through 9):

    CC

    Century (00 to 99)

    YYYY

    Year (0000 to 9999)

    YY

    Year of century (00 to 99)

    ZYY

    YY with leading zeroes suppressed (0 to 99) (TO_CHAR only)

    Q

    Quarter (1 to 4) (TO_CHAR only )

    MM

    Month (01 to 12)

    ZMM

    MM with leading zeroes suppressed (1 to 12) (TO_CHAR only)

    DAYS

    Days since January 1, 0000 (0000000 to 3652436)

    ZDAYS

    DAYS with leading zeroes suppressed (0 to 3652436) (TO_CHAR only)

    DDD

    Day of year (001 to 366)

    ZDDD

    DDD with leading zeroes suppressed (1 to 366) (TO_CHAR only)

    DD

    Day of month (01 to 31)

    ZDD

    DD with leading zeroes suppressed (1 to 31) (TO_CHAR only)

    D

    Day of week (1 to 7) (TO_CHAR only)

    The Z prefix and Q and D are only allowed for the function TO_CHAR. If YY is used without CC, the default CC is 19. The following elements are for representing alphabetic characters:

    MONTH

    Name of month

    MON

    Abbreviated name of month

    DAYOFWEEK

    Name of day

    DAY

    Abbreviated name of day

    -/:.,

    Punctuation marks reproduced in value (includes spaces)

    "string"

    Quoted string reproduced in value

    Delimiting punctuation marks must be the same in the value parameter and the format specification parameter.

  • Capitalization in alphabetic representations follows the capitalization of the corresponding format element. Elements may be represented in uppercase, lowercase, or initial caps. Other mixtures of uppercase and lowercase letters result in an error. For example: 

    'DAYOFWEEK' —[rtrif] MONDAY

'Dayofweek' —[rtrif] Monday

'dayofweek' —[rtrif] monday

'dAyOfWeEk' —[rtrif] error condition

  • Time format is used by the TO_TIME function and by the TO_CHAR functions on TIME expressions. The default format is 'HH:MI:SS'.

    Listed here are formats for elements made up of numeric characters:

    HH or HH24

    Hour of day (00 to 23)

    ZHH or ZHH24

    HH or HH24 with leading zeroes suppressed (0 to 23) (TO_CHAR only)

    HH12

    Hour of day (00 to 12)

    ZHH12

    HH12 with leading zeroes suppressed (0 to 12) (TO_CHAR only)

    MI

    Minute (00 to 59)

    ZMI

    MI with leading zeroes suppressed (0 to 59) (TO_CHAR only)

    SS

    Second (00 to 59)

    ZSS

    SS with leading zeroes suppressed (0 to 59) (TO_CHAR only)

    SECONDS

    Seconds past midnight (00000 to 86399)

    ZSECONDS

    SECONDS with leading zeroes suppressed (0 to 86399) (TO_CHAR only)

    Z is not allowed for the input functions. The following elements are for representing alphabetic characters:

    AM or PM

    AM/PM indicator (use capital letters)

    A.M. or P.M.

    A.M./P.M. indicator with periods (use capital letters)

    ./:.,

    Punctuation marks reproduced in value (includes spaces)

    "string"

    Quoted string reproduced in value

    Delimiting punctuation marks must be the same in the value parameter and the format specification parameter.

  • The TO_DATETIME function and the TO_CHAR function on TIME expressions use the date/time default format 'YYYY-MM-DD HH:MI:SS.FFF'.

    In addition to all formats shown for the date and time format specifications above, the following are also allowed for date/time formats (made up of the numeric characters 0 through 9):

    F

    Tenth of a second (.0 to .9)

    FF

    Hundredth of a second (.00 to .99)

    FFF

    Thousandth of a second (.000 to .999)

  • The TO_INTERVAL function and the TO_CHAR function on INTERVAL expressions use the interval default format 'DAYS HH:MI:SS.FFF'.

    The following formats are allowed in an interval format specification: 

    DAYS             MI            SECONDS           FFF

ZDAYS            ZMI           ZSECONDS          -/:.,

HH or HH24       SS            F                 "string"

ZHH or ZHH24     ZSS           FF
    These were described in the TIME and DATETIME format specifications above.

  • Literals for date/time data types which do not specify all elements of the date/time value are expanded and filled as described below:

    • INTERVAL is zero filled on the left and the right.

    • DATE, TIME, and DATETIME are left-filled with the current values from the system clock, and right-filled with appropriate portions of the default '0000-01-01 00:00:00.000'.

  • When YY is specified in the FormatSpecification and if its value in StringExpression is less than 50, then the century part of DATE and DATETIME defaults to 20, else it is set to 19. This behavior can be overridden by setting the JCW HPSQLSPLITCENTURY to a value between 0 and 100. If the YY part is less than the value of JCW HPSQLSPLITCENTURY then the century part is set to 20, else it is set to 19.

  • Output values are truncated, not rounded, to fit in the specified format.

  • The TO_INTEGER format specification is not optional, and must consist of one of the following single elements only: 

    CC         MM        DAYS        SS

YYYY       DDD       HH or HH24  SECONDS

YY         DD        HH12        F, FF, or FFF

Q          D         MI

  • ADD_MONTHS is a related function. ADD_MONTHS adds a duration of months to a DATE or DATETIME column. Refer to the Add Months Function for further information.

Examples

  1. Date format

    In the example below, the format MM/DD/YY is used to enter a date instead of using the default format, which is YYYY-MM-DD: 

       INSERT INTO ManufDB.TestData(batchstamp, testdate)

        VALUES (TO_DATETIME ('07/02/89 03:20.000', 'MM/DD/YY HH12:MI.FFF'),

                TO_DATE('10/02/84','MM/DD/YY'))
    To return the date entered in the above example, in a format other than the default format, the desired format is specified in the second parameter of the TO_CHAR conversion function: 
       SELECT TO_CHAR(testdate, 'Dayofweek, Month DD')

      FROM ManufDB.TestData

   WHERE labtime < '0 05:00:00.000'
    The value "Friday, July 13" is selected from TestData.

    The following statement inserts different date values depending on the value of the JCW HPSQLSPLITCENTURY, if it is set.

       INSERT INTO ManufDB.TestData(testdata)

           VALUES (TO_DATE ('30/10','YY/MM'))

    Case 1: HPSQLSPLITCENTURY is not set; inserts 2030-10-01

    Case 2: HPSQLSPLITCENTURY is set to 0; inserts 1930-10-01

    Case 3: HPSQLSPLITCENTURY is set to 70; inserts 2030-10-01

  2. Time format 

       INSERT INTO ManufDB.TestData(teststart, batchstamp)

        VALUES (TO_TIME('01:53 a.m.','HH12:MI a.m.'),

             TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM'))

  3. Datetime format 

       UPDATE ManufDB.TestData

      SET batchstamp = TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM')

    WHERE batchstamp = TO_DATETIME('11.01.84 1.11 PM',  'DD.MM.YY HH12.MI PM')

  4. Interval format 

       UPDATE ManufDB.TestData

      SET labtime = TO_INTERVAL('06 10:12:11.111', 'DAYS HH:MI:SS.FFF')

   WHERE testdate = TO_DATE('10.02.84','MM.DD.YY')



Current Functions
  		 


Long Column Functions  
Feedback to webmaster