Hello all,
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett
Combining date and time fields to create a timestamp
Hello,
DS TimeStamp is in fact a string in the "yyyy-mm-dd hh24:mi:ss" format. You can just split and join the two strings you received, something
like:
DATE[1,4]:-:DATE[5,2]:-:DATE[7,2]: :TIME[1,2]:::TIME[... Check the syntax, as i do not have any DS running here to check it.
Then, you can declare it as a timestamp in DS, and you should have no problem to let DS convert it to your DB format.
Regards,
Stephane.
--- Simon Vollett wrote:
> Hello all,
>
> I am running datastage 4.1 on Windows 2000 with SQL server 2000. I
> have a flat file containing a date field in the format yyyymmdd and a
> time field in
> the format "hhmmss". What I want to do is take these two fields and
> merge
> them into a single datetime field in the format yyyy-mm-dd hh:mm:ss .
> I
> have tried using iconv and oconv, this is my best effort so far
>
>
> Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "),
> Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"),
> Left(Right(DSLink3.call_time,4),2)), ":"),
> Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
>
> I have no problem outputting the string created to a flat file, but
> cannot convert it into a format that will be accepted by my database.
> Any hints or
> suggestions appreciated.
>
> Regards,
>
> Simon Vollett
>
>
>
=====
|
@..@ |
(----) | - - - - - - - - - - - - - - - - - - - - - - -
( >__< ) | Stephane TISSOT
^^ ~~ ^^ | (stissot@yahoo.com) (stephane_tissot@aldec.fr)
__________________________________________________
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com
DS TimeStamp is in fact a string in the "yyyy-mm-dd hh24:mi:ss" format. You can just split and join the two strings you received, something
like:
DATE[1,4]:-:DATE[5,2]:-:DATE[7,2]: :TIME[1,2]:::TIME[... Check the syntax, as i do not have any DS running here to check it.
Then, you can declare it as a timestamp in DS, and you should have no problem to let DS convert it to your DB format.
Regards,
Stephane.
--- Simon Vollett wrote:
> Hello all,
>
> I am running datastage 4.1 on Windows 2000 with SQL server 2000. I
> have a flat file containing a date field in the format yyyymmdd and a
> time field in
> the format "hhmmss". What I want to do is take these two fields and
> merge
> them into a single datetime field in the format yyyy-mm-dd hh:mm:ss .
> I
> have tried using iconv and oconv, this is my best effort so far
>
>
> Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "),
> Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"),
> Left(Right(DSLink3.call_time,4),2)), ":"),
> Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
>
> I have no problem outputting the string created to a flat file, but
> cannot convert it into a format that will be accepted by my database.
> Any hints or
> suggestions appreciated.
>
> Regards,
>
> Simon Vollett
>
>
>
=====
|
@..@ |
(----) | - - - - - - - - - - - - - - - - - - - - - - -
( >__< ) | Stephane TISSOT
^^ ~~ ^^ | (stissot@yahoo.com) (stephane_tissot@aldec.fr)
__________________________________________________
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com
How about:
DSLink3.call_date[1,4]:"-":DSLink3.call_date[5,2]:"-":DSLink3.call_date[7,2]
:Space(1):DSLink3.call_time[1,2]:":":DSLink3.call_time[3,2]:":":DSLink3.call
_time[5,2]:".000"
You could use " " rather than Space(1) for slightly more efficiency; I wanted to thwart the line wrap in most email software.
I would put this expression into a two-argument Transform, so that the expression could be re-used.
-----Original Message-----
From: Simon Vollett [mailto:svollett@comdyn.com.au]
Sent: Wednesday, 03 October 2001 17:26
To: datastage
Subject: Combining date and time fields to create a timestamp
Hello all,
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett
DSLink3.call_date[1,4]:"-":DSLink3.call_date[5,2]:"-":DSLink3.call_date[7,2]
:Space(1):DSLink3.call_time[1,2]:":":DSLink3.call_time[3,2]:":":DSLink3.call
_time[5,2]:".000"
You could use " " rather than Space(1) for slightly more efficiency; I wanted to thwart the line wrap in most email software.
I would put this expression into a two-argument Transform, so that the expression could be re-used.
-----Original Message-----
From: Simon Vollett [mailto:svollett@comdyn.com.au]
Sent: Wednesday, 03 October 2001 17:26
To: datastage
Subject: Combining date and time fields to create a timestamp
Hello all,
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett
Thanks everyone for your help.
My problem now is that I cannot convert this string YYYY-MM-DD hh:mi:ss into a suitable format to have it imported into a SQL server datetime field. I try using the skd transform DateTimeStampToODBCWithTime, yet it will not convert the string giving an error message tust.CTransformerStage2
(DateGenericToTimeStamp): Could not convert date/time :2001-02-01 10:40:53. The string is then treated as a null and the job fails. Does anyone know why this might be happening aor are there any other transforms that I can try?
Regards,
Simon V
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Thursday, October 04, 2001 9:42 AM
To: datastage-users@oliver.com
Subject: RE: Combining date and time fields to create a timestamp
How about:
DSLink3.call_date[1,4]:"-":DSLink3.call_date[5,2]:"-":DSLink3.call_date[7,2]
:Space(1):DSLink3.call_time[1,2]:":":DSLink3.call_time[3,2]:":":DSLink3.call
_time[5,2]:".000"
You could use " " rather than Space(1) for slightly more efficiency; I wanted to thwart the line wrap in most email software.
I would put this expression into a two-argument Transform, so that the expression could be re-used.
-----Original Message-----
From: Simon Vollett [mailto:svollett@comdyn.com.au]
Sent: Wednesday, 03 October 2001 17:26
To: datastage
Subject: Combining date and time fields to create a timestamp
Hello all,
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett
My problem now is that I cannot convert this string YYYY-MM-DD hh:mi:ss into a suitable format to have it imported into a SQL server datetime field. I try using the skd transform DateTimeStampToODBCWithTime, yet it will not convert the string giving an error message tust.CTransformerStage2
(DateGenericToTimeStamp): Could not convert date/time :2001-02-01 10:40:53. The string is then treated as a null and the job fails. Does anyone know why this might be happening aor are there any other transforms that I can try?
Regards,
Simon V
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Thursday, October 04, 2001 9:42 AM
To: datastage-users@oliver.com
Subject: RE: Combining date and time fields to create a timestamp
How about:
DSLink3.call_date[1,4]:"-":DSLink3.call_date[5,2]:"-":DSLink3.call_date[7,2]
:Space(1):DSLink3.call_time[1,2]:":":DSLink3.call_time[3,2]:":":DSLink3.call
_time[5,2]:".000"
You could use " " rather than Space(1) for slightly more efficiency; I wanted to thwart the line wrap in most email software.
I would put this expression into a two-argument Transform, so that the expression could be re-used.
-----Original Message-----
From: Simon Vollett [mailto:svollett@comdyn.com.au]
Sent: Wednesday, 03 October 2001 17:26
To: datastage
Subject: Combining date and time fields to create a timestamp
Hello all,
I am running datastage 4.1 on Windows 2000 with SQL server 2000. I have a flat file containing a date field in the format yyyymmdd and a time field in the format "hhmmss". What I want to do is take these two fields and merge them into a single datetime field in the format yyyy-mm-dd hh:mm:ss . I have tried using iconv and oconv, this is my best effort so far
Cats(Cats(Cats(Oconv(Iconv(DSLink3.call_date, "D"), "D-YMD"), " "), Oconv(Iconv(Cats(Cats(Cats(Cats(Left(DSLink3.call_time,2), ":"), Left(Right(DSLink3.call_time,4),2)), ":"), Right(DSLink3.call_time,2)), "MTS"), "MTS")),".000")
I have no problem outputting the string created to a flat file, but cannot convert it into a format that will be accepted by my database. Any hints or suggestions appreciated.
Regards,
Simon Vollett
The same thing, still the failure, but in the mean time I have had success using the transform DateYearFirstToODBCWithTime. Looking at the internal documentation for both these transforms in Manager, it would seem that both should do the trick, but in the help documentation the DateYearFirst transforms state explicitly that The strings can also contain a time entry in the format HH:MM:SS:SSS, HH:MM:SS or HH:MM, which is not the case for the generic transforms. So I gave it a go and it worked and I am now v happy. Thanks for your help though.
Regards,
SimonV
Ardent DataStage Release 4.1.1 Developers Help
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Thursday, October 04, 2001 10:49 AM
To: datastage-users@oliver.com
Subject: RE: Combining date and time fields to create a timestamp
What happens if you declare the data element for this column to be TimeStamp?
Regards,
SimonV
Ardent DataStage Release 4.1.1 Developers Help
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Thursday, October 04, 2001 10:49 AM
To: datastage-users@oliver.com
Subject: RE: Combining date and time fields to create a timestamp
What happens if you declare the data element for this column to be TimeStamp?
Hi Simon,
is the SQL Server users language the same of Datastage Server/Client?
Regards,
Riccardo
----- Original Message -----
From: "Simon Vollett"
To:
Sent: Thursday, October 04, 2001 2:59 AM
Subject: RE: Combining date and time fields to create a timestamp
> The same thing, still the failure, but in the mean time I have had
> success using the transform DateYearFirstToODBCWithTime. Looking at
> the internal documentation for both these transforms in Manager, it
> would seem that
both
> should do the trick, but in the help documentation the DateYearFirst
> transforms state explicitly that The strings can also contain a time
entry
> in the format HH:MM:SS:SSS, HH:MM:SS or HH:MM, which is not the case
> for the generic transforms. So I gave it a go and it worked and I am
> now v happy. Thanks for your help though.
>
> Regards,
>
> SimonV
>
> Ardent DataStage Release 4.1.1 Developers Help
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Thursday, October 04, 2001 10:49 AM
> To: datastage-users@oliver.com
> Subject: RE: Combining date and time fields to create a timestamp
>
>
> What happens if you declare the data element for this column to be
> TimeStamp?
>
>
>
>
is the SQL Server users language the same of Datastage Server/Client?
Regards,
Riccardo
----- Original Message -----
From: "Simon Vollett"
To:
Sent: Thursday, October 04, 2001 2:59 AM
Subject: RE: Combining date and time fields to create a timestamp
> The same thing, still the failure, but in the mean time I have had
> success using the transform DateYearFirstToODBCWithTime. Looking at
> the internal documentation for both these transforms in Manager, it
> would seem that
both
> should do the trick, but in the help documentation the DateYearFirst
> transforms state explicitly that The strings can also contain a time
entry
> in the format HH:MM:SS:SSS, HH:MM:SS or HH:MM, which is not the case
> for the generic transforms. So I gave it a go and it worked and I am
> now v happy. Thanks for your help though.
>
> Regards,
>
> SimonV
>
> Ardent DataStage Release 4.1.1 Developers Help
>
> -----Original Message-----
> From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
> Sent: Thursday, October 04, 2001 10:49 AM
> To: datastage-users@oliver.com
> Subject: RE: Combining date and time fields to create a timestamp
>
>
> What happens if you declare the data element for this column to be
> TimeStamp?
>
>
>
>