|
Learn more about Toad for SQL Server Find solutions and downloads at the |
|
|
Learn more about Toad for SQL Server Find solutions and downloads at the |
|
Toad for SQL Server 6.6 |
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.
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 |
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 |
||||||||
| Round() |
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:
Round(To_date('10-12-2012', 'DD-MM-YYYY'), 'month') returns 01-12-2012 Round(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. |