Chat now with support
Chat with Support

Toad Data Point 6.2.3 - User Guide

Using Date Formats in Automation

You can customize the format of a date/time suffix used in a file name in an Automation activity. You can also customize the format of a date/time variable that you create in the Set Variable activity. Use the date/time formats described in the following table.

When building an expression in the expression editor in an Automation activity, Toad provides a list of functions to use. The functions provided mimic, as close as possible, the Oracle® PL/SQL functions. Users familiar with Oracle functions will find the syntax slightly different, however.

Date and Time Formats

Use the following date/time formats.

Format Description
DD

Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero.

If the date is January 2

'DD-MM' displays 02-01

MM

Represents the month as a number from 01 through 12. A single-digit month is formatted with a leading zero.

If the date is January 2

'DD-MM' displays 02-01

YYYY

Represents the year as a four-digit number. If the year has fewer than four digits, the number is padded with leading zeros to achieve four digits.

If the date is January 1, 2010

'YYYY' displays 2010

YYY

Represents the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to achieve three digits.

If the year is 2010

'YYY' displays 2010

YY

Represents the year as a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the two-digit year has fewer than two significant digits, the number is padded with leading zeros to achieve two digits.

If the year is 2010

'YY' displays 10

If the year is 2008

'YY' displays 08

Y

Represents the year as a one or two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the first digit of a two-digit year begins with a zero (for example, 2008), the number is formatted without a leading zero.

If the year is 2010

'Y' displays 10

If the year is 2008

'Y' displays 8

HH

Represents the hour as a number from 1 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon. A particular hour after midnight is indistinguishable from the same hour after noon. The hour is not rounded, and a single-digit hour is formatted without a leading zero.

If the time is 5:43 am or 5:43 pm

'HH' displays 5

If the time is 11:43 am or 11:43 pm

'HH' displays 11

HH12 Same as 'HH'
HH24

Represents the hour as a number from 0 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted without a leading zero.

If the time is 5:43 am

'HH24' displays 05

If the time is 5:43 pm

'HH24' displays 17

If the time is 11:43 pm

'HH24' displays 23

MI

Represents the minute as a number from 0 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted with a leading zero.

If the time is 4:09

'MI' displays 09

SS

Represents the seconds as a number from 0 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted with a leading zero.

If the time is 5:43:02

'SS' displays 02

SS.SS

<seconds as a number from 00 through 59>.<hundredths of a second as a number from 00 through 99>.

If the time is 5:43:02:01

'SS.SS' displays 02.01

AM, PM Use either to add AM or PM designation. Toad always adds the correct designation whether you use 'AM' or 'PM'.

Use the following delimiters in datetime formats.

Delimiter Name
' ' blank space (do not include the quotes)
_ underscore
- dash
/ forward slash
, comma
. period
; semicolon
: colon

Using Date Functions

Use the following examples to help you format Date functions in Automation.

Function Description
To_char()

Converts a date value in the 1st parameter to a string value using the format specified in the 2nd parameter.

If the date is December 21, 2012

To_char(Current_date(), 'DD-MM-YYYY') returns 21-12-2012

Extract()

Extracts and returns the value of a specified DateTime field (1st parameter) from a DateTime value expression (2nd parameter).

1st parameter can be any of the following strings:

'year'

'month'

'day'

'hour'

'minute'

'second'

If the date is December 21, 2012 and the time is 5:43 pm

Extract('year', Current_date()) returns 2012

Extract('hour', Localtimestamp()) returns 17

RoundDate()

Returns date in 1st parameter rounded to the unit specified by the format in the 2nd parameter. If you omit the 2nd parameter, date is rounded to the nearest day.

2nd parameter can be any of the following strings:

'day', 'd', 'dy' Rounds up or down (from Wednesday noon) to the nearest beginning of the week (Sunday).
'month', 'mon', 'mm' Rounds up or down (from the 16th day of the month) to the nearest first day of the month.
'hh', 'hh12', 'hh24' Rounds up or down (from middle of hour) to the nearest hour.
'mi' Rounds up or down (from middle of minute) to the nearest minute.

 

RoundDate(To_date('10-12-2012', 'DD-MM-YYYY'), 'month')

returns 01-12-2012

RoundDate(To_date('17-12-2012', 'DD-MM-YYYY'), 'month')

returns 01-01-2013

To_date()

Converts the character value in the 1st parameter to a value of DATE data type using the format specified in the 2nd parameter.

 

to_date('07/20/58 12:13:45', 'MM/DD/YY HH:MI:SS')

returns 7/20/1958 12:13:45 AM

to_date('07201958 12:14:55.35', 'MMDDYYYY HH:MI:SS.SS')

returns 7/20/1958 12:14:55.35 AM

Note: The string in the 1st parameter must use the exact format in the 2nd parameter.

  

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating