Author |
Message |
Kel
Participant
Joined: 11 May 2015
Posts: 31
Location: Robinsons Cybergate Tower 2
Points: 451
|
|
DataStage® Release: 8x |
Job Type: Parallel |
OS: Windows |
|
Hi,
I have an input string (varchar) coming from a sequential file which looks like:
yyyymmdd
I want it on my output dataset to become sql type = date with same format yyyymmdd.
Tried solution:
1. I tried using StringToDate("yyyymmdd", "%yyyy%mm%dd%") but on output viewdata it becomes asterisk (*).
2. I also tried to format it on the sequential file type defaults, set Format string = %yyyy%mm%dd and set sql type = date. But it becomes yyyy-mm-dd on viewdata.
3. I also tried to set the project default for date to %yyyy%mm%dd. but still not working.
-Can sum1 help what am I doing wrong.
Thank you!
|
_________________ BOG |
|
|
 |
ShaneMuir

Group memberships: Premium Members
Joined: 15 Jun 2004
Posts: 507
Location: London
Points: 4387
|
|
|
|
|
|
You are doing nothing wrong.
You are converting the data from a string to a date. Once converted, it will be "represented" as is the default in whichever tool you are using to view the data.
In you target SQL - it will be stored as a date value in some sort of internal format, but when it is shown to a user it will be as that user requests it to be shown, in your case as YYYY-MM-DD.
|
|
|
|
 |
Kel
Participant
Joined: 11 May 2015
Posts: 31
Location: Robinsons Cybergate Tower 2
Points: 451
|
|
|
|
|
|
Hi Shane,
Ahm so once I converted it using StringToDate its ouput would be a date format right? But on my mapping document it needs the format to be yyyymmdd. Hmmm. Im so lost.
|
_________________ BOG |
|
|
 |
priyadarshikunal

Group memberships: Premium Members
Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9319
|
|
|
|
|
|
The date has no specific storage format like YYYYMMDD. It is stored in database's internal format and only display format will change.
The format you provide in string to date is the format of your input string and the defined format is used to convert the input string to date. If you want to store it as YYYYMMDD then I think you have to store it as VarChar or Char.
|
_________________ Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.  |
|
|
 |
Kel
Participant
Joined: 11 May 2015
Posts: 31
Location: Robinsons Cybergate Tower 2
Points: 451
|
|
|
|
|
|
Hi Priyadarshi,
Thank you for you explanation. That cleared my confusion. Thanks.
|
_________________ BOG |
|
|
 |
ShaneMuir

Group memberships: Premium Members
Joined: 15 Jun 2004
Posts: 507
Location: London
Points: 4387
|
|
|
|
|
|
Kel wrote: |
Ahm so once I converted it using StringToDate its ouput would be a date format right? But on my mapping document it needs the format to be yyyymmdd. Hmmm. Im so lost. |
So I take it from this that you are loading your values into a DB then outputting them again to some sort of text file?
If this is the case - depending on your requirements - you wouldn't need to do a format change at all (as your input matches your required output?)
However I would be inclined to store the date as a date in your DB then cast it to its required format when selecting data from the DB - this way if requirements were to change its a bit more flexible.
EDIT: Sorry just re-read your post, and noticed you were going to a dataset. But my point above remains (sort of). What you store the date as (either date or varchar) will ultimately depend on how you intend to use that data.
|
|
|
|
 |
|