Dates/Times from uv to SQL

[admin]

How should my dictionary and column definitions be defined to properly insert universe date and time fields into a SQL timestamp field? I cant get it to work properly.

Thanks,
Mike Roosa

[admin]

UniVerse SQL does not support the TimeStamp data type. Therefore DataStage can not use it.

You have two possible routes, either create an I-descriptor to generate a timestamp, but declare it as Char(19) in the DataStage metadata, or import the date and time as separate columns then use a column derivation to generate the TimeStamp.

> ----------
> From: Roosa, Mike[SMTP:Mike.Roosa@getronics.com]
> Reply To: informix-datastage@oliver.com
> Sent: Thursday, 3 August 2000 10:58
> To: informix-datastage@oliver.com
> Subject: Dates/Times from uv to SQL
>
> How should my dictionary and column definitions be defined to properly
> insert universe date and time fields into a SQL timestamp field? I
> cant get it to work properly.
>
> Thanks,
> Mike Roosa
>

[admin]

I should have it explained it a little better.

I have a dict item called CALL_DATE which has an output conversion of D2/ and a dict item called CALL_TIME with an output conversion of MTS. The goal is to get CALL_DATE into a SQL 7.0 column called CallDate which is a timestamp type and CALL_TIME into a column called CallTime which is a timestamp type.

Here is what Ive done so far. I went into the transformer stage and set the SQL type for CALL_DATE to Date and the Data Element to timestamp. For CALL_TIME, I set SQL type to Time and the Data Element to timestamp. I set the SQL type for CallDate and CallTime to Timestamp.

When I run the job, it insert the CALL_DATE correctly into SQL 7.0 but appends 00:00:00 which indicates no time was entered. However, the CALL_TIME inserts as current date and CALL_TIME which is a problem. Do we have to have it enter the current date?

Hope this explains the problem a little better.

Thanks.

-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@informix.com]
Sent: Wednesday, August 02, 2000 9:11 PM
To: informix-datastage@oliver.com
Subject: RE: Dates/Times from uv to SQL


UniVerse SQL does not support the TimeStamp data type. Therefore DataStage can not use it.

You have two possible routes, either create an I-descriptor to generate a timestamp, but declare it as Char(19) in the DataStage metadata, or import the date and time as separate columns then use a column derivation to generate the TimeStamp.

> ----------
> From: Roosa, Mike[SMTP:Mike.Roosa@getronics.com]
> Reply To: informix-datastage@oliver.com
> Sent: Thursday, 3 August 2000 10:58
> To: informix-datastage@oliver.com
> Subject: Dates/Times from uv to SQL
>
> How should my dictionary and column definitions be defined to properly
> insert universe date and time fields into a SQL timestamp field? I
> cant get it to work properly.
>
> Thanks,
> Mike Roosa
>

[admin]

Mike,

There is an existing DataStage function called TimeStampFull, which can be used to create a timestamp. I have included the code below. Hope this helps.

Mark


Transforms a timestamp or Date input depending on second argument: "TIMESTAMP" => produces a timestamp with time = 00:00:00 from a date
"FULL" => Produces a full Timestamp from both a date and
time entry
"DATE" => produces an internal date from a timestamp (time part
ignored)
"TIME" => produces an internal time from a timestamp (date part
ignored)
(A timestamp is a string in the form "YYYY-MM-DD HH:MM:SS".)

*******************************************************************
* Copyright (c) 2000 Informix Software Inc. - All Rights Reserved.*
* This code may be copied on condition that this copyright *
* notice is included as is in any code derived from this source. *
*******************************************************************
*
************************************************************
* Input Args
************************************************************
Inputs:
DateStamp = Arg1
TimeStamp = Arg2
StampType = Arg3

*
************************************************************
* Initialize variables
************************************************************
Init:
PgmName = "TimeStampFull"
Verbose = @TRUE
Ans = 0

*
************************************************************
* main case
************************************************************
Main:

Begin Case
Case StampType = "TIMESTAMP"

* check the input data
Gosub DateCheck

* Convert an Internal Date to a TIMESTAMP.
Ans = Oconv(DateStamp,"D-YMD[4,2,2]"):" 00:00:00"

Case StampType = "FULL"

* check the input data
Gosub DateCheck
Gosub TimeCheck

* Convert an Internal Date to First Half of TIMESTAMP.
DatePart = Oconv(DateStamp,"D-YMD[4,2,2]")

* Convert an Internal Time to Second Half of TIMESTAMP.
TimePart = Oconv(TimeStamp,"MTS:")

* Force midnight for now!
* Ans = DatePart:" ":TimePart

Case Arg3 = "YY"
* convert year to date
DateStamp = "01/01/" : DateStamp

* check the input data
Gosub DateCheck
Gosub TimeCheck

* Convert an Internal Date to First Half of TIMESTAMP.
DatePart = Oconv(DateStamp,"D-YMD[4,2,2]")

* Convert an Internal Time to Second Half of TIMESTAMP.
TimePart = Oconv(TimeStamp,"MTS:")

* Force midnight
Ans = DatePart:" ":"00:00:00"

Case Arg3 = "DATE"
* extract date
DateStamp = MatchField(DateStamp,"0X 2N:2N:2N",1)

* verify the extract
If len(DateStamp) = "" Then
Gosub DateCheck
End

* Convert a TIMESTAMP to an Internal Date.
Ans = Iconv(DateStamp,"D-YMD[4,2,2]")

Case Arg3 = "TIME"
* extract the time
TimeStamp = MatchField(DateStamp,"4N-2N-2N 0X",7)

* verify the extract
If Len(TimeStamp) = "" Then
Gosub TimeCheck
End

* Convert a TIMESTAMP to an Internal Time.
Ans = Iconv(TimeStamp,"MTS:")

CASE 1 ; * all other cases
Message = "Invalid code ": StampType:", returned zero."
Gosub WarningMessage
Ans = 0
GoTo TheEnd
End Case

GoTo TheEnd
*
************************************************************
* function DateCheck
*
* verify the date is not in output format and is numeric
*
* In : DateStamp
* Out : DateStamp
* Local : Message,
************************************************************
DateCheck:

* empty string
If DateStamp = "" THEN
Message = "No date input, zero used."
Gosub WarningMessage
Ans = "0"
GoTo TheEnd
End

* format check
If Index(DateStamp,"/",1) Then
DateStamp = Iconv(DateStamp,"D2/")
End

* numeric check
If Not(Num(DateStamp)) Then
Message = "Non-numeric date input ": DateStamp :", zero used."
Gosub WarningMessage
Ans = "0000-00-00 00:00:00"
GoTo TheEnd
End

Return(DateStamp)

*
************************************************************
* Sub TimeCheck
*
* verify the time is not in output format and is numeric
*
* In : TimeStamp
* Out : TimeStamp
* Local : Message
************************************************************
TimeCheck:

* empty string
If TimeStamp = "" THEN
Message = "No time input, zero used."
Gosub WarningMessage
Ans = "0"
GoTo TheEnd
End

* format check
If Index(TimeStamp,":",1) Then
TimeStamp = Iconv(TimeStamp,"MTS:")
End

* numeric check
If Not(Num(TimeStamp)) Then
Message = "Non-numeric Time input ": TimeStamp :", zero used."
Gosub WarningMessage
Ans = "0000-00-00 00:00:00"
GoTo TheEnd
End

Return(TimeStamp)
*
************************************************************
* Sub InfoMessage(Verbose As Integer)
*
* sends message to director log
*
* In : Message, PgmName, Verbose
************************************************************
InfoMessage:

If Verbose Then
Crt Message
End

Call DSLogInfo(Message,PgmName)

Return (Ans)

*
************************************************************
* Sub WarningMessage(Verbose As Integer)
*
* sends message to director log
*
* In : Message, PgmName, Verbose
************************************************************
WarningMessage:
*
If Verbose Then
Crt Message
Call DSLogWarn(Message,PgmName)
End
Ans = @FALSE
Return (Ans)

*
************************************************************
* Sub FatalMessage(Verbose As Integer)
*
* sends message to director log and exits
*
* In : Message, PgmName, Verbose
************************************************************
FatalMessage:
*
If Verbose Then
Crt Message
Call DSLogFatal(Message,PgmName)
End
Ans = @FALSE
Goto TheEnd

*
************************************************************
* Thats All Folks
************************************************************
TheEnd:




-----Original Message-----
From: Roosa, Mike [mailto:Mike.Roosa@getronics.com]
Sent: Thursday, August 03, 2000 8:15 AM
To: informix-datastage@oliver.com
Subject: RE: Dates/Times from uv to SQL


I should have it explained it a little better.

I have a dict item called CALL_DATE which has an output conversion of D2/ and a dict item called CALL_TIME with an output conversion of MTS. The goal is to get CALL_DATE into a SQL 7.0 column called CallDate which is a timestamp type and CALL_TIME into a column called CallTime which is a timestamp type.

Here is what Ive done so far. I went into the transformer stage and set the SQL type for CALL_DATE to Date and the Data Element to timestamp. For CALL_TIME, I set SQL type to Time and the Data Element to timestamp. I set the SQL type for CallDate and CallTime to Timestamp.

When I run the job, it insert the CALL_DATE correctly into SQL 7.0 but appends 00:00:00 which indicates no time was entered. However, the CALL_TIME inserts as current date and CALL_TIME which is a problem. Do we have to have it enter the current date?

Hope this explains the problem a little better.

Thanks.

-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@informix.com]
Sent: Wednesday, August 02, 2000 9:11 PM
To: informix-datastage@oliver.com
Subject: RE: Dates/Times from uv to SQL


UniVerse SQL does not support the TimeStamp data type. Therefore DataStage can not use it.


You have two possible routes, either create an I-descriptor to generate a timestamp, but declare it as Char(19) in the DataStage metadata, or import the date and time as separate columns then use a column derivation to generate the TimeStamp.

> ----------
> From: Roosa, Mike[SMTP:Mike.Roosa@getronics.com]
> Reply To: informix-datastage@oliver.com
> Sent: Thursday, 3 August 2000 10:58
> To: informix-datastage@oliver.com
> Subject: Dates/Times from uv to SQL
>
> How should my dictionary and column definitions be defined to properly
> insert universe date and time fields into a SQL timestamp field? I
> cant get it to work properly.
>
> Thanks,
> Mike Roosa
>

[admin]

Mike,

You dont have a DataStage problem, you have a definition problem. Basically it is your call. A date in SQL Server has a date and time component - you have to decide what you want to put in it. I would have suggested combining your call date and call time into a single SQL 7.0 field. It comes back to the definition of your SQL 7.0 database and how the fields are going to be used.

If you want to keep separate fields (and I can see reasons why that might be
desirable) I would suggest that the best thing to put in the date part of your CallTime column is the call date. I wouldnt put the current date.

This means that for example, CallDate might contain 2000-03-06 00:00:00 and CallTime might contain 2000-03-06 15:10:34. We have situations where we do this in our own data warehouse.

If you want to do things like look at what time of day you tend to get calls, which means you really dont care about the date when looking at the call time (eg graphing call frequency by time of day), you might want to consider another data type other than SQL Servers date. Suggestions might include the number of seconds since midnight (now that ought to be easy to
convert from UniVerse ) or perhaps the fraction of a day. That is, 6am
= 0.25, midday = 0.5, 6pm = 0.75. Either of these would allow you to do meaningful analysis, calculations etc. For display purposes, you might also want to store the time as a string, eq "15:10:34", but this is not really useful for anything but displaying. If the target database is a data warehouse, then it may even make sense to store it in any or all of the formats I have described here.

To put it another way, focus on what you expect to do with and get out of your SQL 7.0 database and design it accordingly. DataStage can do whatever you need to achieve this. (And dont be tempted to adjust your design for _perceived_ limitations of DataStage.)

Cheers

David
-----Original Message-----
From: Roosa, Mike [SMTP:Mike.Roosa@getronics.com]
Sent: Thursday, 3 August 2000 22:15
To: informix-datastage@oliver.com
Subject: RE: Dates/Times from uv to SQL

I should have it explained it a little better.

I have a dict item called CALL_DATE which has an output conversion of D2/
and a dict item called CALL_TIME with an output conversion of MTS. The goal
is to get CALL_DATE into a SQL 7.0 column called CallDate which is a
timestamp type and CALL_TIME into a column called CallTime which is a
timestamp type.

Here is what Ive done so far. I went into the transformer stage and set
the SQL type for CALL_DATE to Date and the Data Element to timestamp. For
CALL_TIME, I set SQL type to Time and the Data Element to timestamp. I set
the SQL type for CallDate and CallTime to Timestamp.

When I run the job, it insert the CALL_DATE correctly into SQL 7.0 but
appends 00:00:00 which indicates no time was entered. However, the
CALL_TIME inserts as current date and CALL_TIME which is a problem. Do we
have to have it enter the current date?

Hope this explains the problem a little better.

Thanks.

-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@informix.com]
Sent: Wednesday, August 02, 2000 9:11 PM
To: informix-datastage@oliver.com
Subject: RE: Dates/Times from uv to SQL


UniVerse SQL does not support the TimeStamp data type. Therefore DataStage
can not use it.

You have two possible routes, either create an I-descriptor to generate a
timestamp, but declare it as Char(19) in the DataStage metadata, or import
the date and time as separate columns then use a column derivation to
generate the TimeStamp.

> ----------
> From: Roosa, Mike[SMTP:Mike.Roosa@getronics.com]
> Reply To: informix-datastage@oliver.com
> Sent: Thursday, 3 August 2000 10:58
> To: informix-datastage@oliver.com
> Subject: Dates/Times from uv to SQL
>
> How should my dictionary and column definitions be defined to properly
> insert universe date and time fields into a SQL timestamp field? I cant
> get it to work properly.
>
> Thanks,
> Mike Roosa
>


*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else

*************************************************************************