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
>