DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
Kel
Participant



Joined: 11 May 2015
Posts: 31
Location: Robinsons Cybergate Tower 2
Points: 451

Post Posted: Mon Jun 29, 2015 2:25 am Reply with quote    Back to top    

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

Post Posted: Mon Jun 29, 2015 3:06 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Kel
Participant



Joined: 11 May 2015
Posts: 31
Location: Robinsons Cybergate Tower 2
Points: 451

Post Posted: Mon Jun 29, 2015 3:38 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9319

Post Posted: Mon Jun 29, 2015 3:51 am Reply with quote    Back to top    

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. Wink
Rate this response:  
Not yet rated
Kel
Participant



Joined: 11 May 2015
Posts: 31
Location: Robinsons Cybergate Tower 2
Points: 451

Post Posted: Mon Jun 29, 2015 3:57 am Reply with quote    Back to top    

Hi Priyadarshi,

Thank you for you explanation. That cleared my confusion. Thanks.

_________________
BOG
Rate this response:  
Not yet rated
ShaneMuir



Group memberships:
Premium Members

Joined: 15 Jun 2004
Posts: 507
Location: London
Points: 4387

Post Posted: Mon Jun 29, 2015 4:30 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours