Data Handling

How do I compare a date that is in YYYYMMDD character format to today’s date?

Use the DATE2STR function with $NOW:

define constant_date : char(8) value “20000323”
if date2str($now,”YYYYMMDD”) = constant_date
print “is today’s date : “, constant_date
else
print “is not today’s date : “, constant_date
endif

How can I determine the day of the week for today’s date?

Use the DATE2STR function with $NOW. Sunday is day 1 and Saturday is day 7.

1> define dt : date
2> define s1 : string
3> define s2 : string
4> setvar dt = $now
5> setvar s1 = date2str(dt,”D”)
6> setvar s2 = date2str(dt,”DAY”)
7> print ‘the day of the week for’,s2,dt,’is’,s1

the day of the week for THURSDAY 08-MAR-2001 is 5

How do I calculate the number of days between today and a supplied constant? 

The result of my calculation is wrong. Here’s what I’m doing:
1> define date1 : numeric
2> define date2 : numeric
3> setvar date1 = $now
4> setvar date2 = str2date(“20000101”)
5> print date1 – date2
630.000000

Warehouse provides an implicit DATETIME-to-NUMERIC conversion in the case where the target variable is a NUMERIC data type such as date1 and date2. In this example, the Warehouse constant $NOW and the built-in function STR2DATE both return DATETIME data types that are then converted to NUMERIC by the SETVAR statements.

Consequently the operation on line 5 is a numeric subtraction, not a date subtraction. Assuming that today’s date is mid-afternoon July 31, 2000, this operation is:

20000731.000000 – 200000101.000000 = 630.000000

In order to correctly calculate the calendar difference, subtract DATETIME data types from each other. An example:

print $now – str2date(“20000101”) will print 212 15:32:12

Subtracting one DATETIME type from another results in an INTERVAL date type. An INTERVAL type keeps track of fractional days expressed as time as well as whole days.

Warehouse also provides an implicit INTERVAL-to-NUMERIC conversion in cases where the target variable is a NUMERIC data type. By converting from an INTERVAL to a NUMERIC you lose the time precision of the INTERVAL and the result is a number, in this case 212.000000.

Where can I find the result types of addition and subtraction operations between dateoperators?

In the Reference Manual, of course. The following table summarizes that information:

What is the syntax for setting columns of type Oracle DATE?

Let’s assume that in an Oracle database with open file handle db there is a column sched in table foothat has been created as follows:

direct db, “create table foo (event char(20) primary key, sched date)”

To variously populate the rows by means of the compile-time DIRECT statement:

direct db, “insert into foo values (‘vacation_start’, null)”
direct db, “insert into foo values (‘birthday_ time’, to_date(‘19970811211405′,’YYYYMMDDHH24MISS’) )”
direct db, “insert into foo values (‘today’, sysdate)”

Alternatively, to populate the rows by means of the run-time COPY after using the Warehouse SETVAR statement to assign a value to the sched column:

setvar sched = $null
setvar sched = str2date(“19970811 211405″,”YYYYMMDD HH24MISS”)
setvar sched = $today

What happens if I neglect to set an element in a variable defined as a Warehouse DATETIME data type?

The Warehouse DATETIME data type is a composite data type. Defaulting will occur for any element that has not been explicitly set.  The following code describes this:

1>
2>
3> define hours_minutes : string value “2230”
4> define dt : datetime
5>
6> * show the defaults
7> print date2str(dt,”YYYY-MM-DD HH24:MI”)
8>
9> * this initialization might not produce the results you expect
10> setvar dt = str2date(hours_minutes,”HH24MI”)
11> print date2str(dt,”YYYY-MM-DD HH24:MI”)
12>
13> * this initialization will
14> setvar dt = str2date(convert($today, “string”) ||  hours_minutes,”YYYYMMDDHH24MI”)
15> print date2str(dt,”YYYY-MM-DD HH24:MI”)

1901-01-01 00:00
1901-01-01 22:30
2000-12-21 22:30

The default values assigned to the Warehouse DATE and the TIME data types are identical to the default values assigned to DATETIME:

1> define d : date
2> define t : time
3>
4> print date2str(d,”YYYY-MM-DD”)
5> print date2str(t,”HH24:MI:SS”)

1901-01-01
00:00:00

How can I store a date/timestamp in milliseconds into a variable?

Here’s one of several approaches to accomplishing this:

1> define x : x24
2> setvar x = $now pic “yyyy-mm-dd hh24:mi:ss.ttt”
3> print x
2004-06-25 07:58:45.612

Note that sub-second time resolution differs from platform to platform.