Date Problems

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Kel
Participant
Posts: 31
Joined: Mon May 11, 2015 3:20 am
Location: Robinsons Cybergate Tower 2
Contact:

Date Problems

Post by Kel »

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
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Post by ShaneMuir »

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
Posts: 31
Joined: Mon May 11, 2015 3:20 am
Location: Robinsons Cybergate Tower 2
Contact:

Post by Kel »

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
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

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:
Kel
Participant
Posts: 31
Joined: Mon May 11, 2015 3:20 am
Location: Robinsons Cybergate Tower 2
Contact:

Post by Kel »

Hi Priyadarshi,

Thank you for you explanation. That cleared my confusion. Thanks.
BOG
ShaneMuir
Premium Member
Premium Member
Posts: 508
Joined: Tue Jun 15, 2004 5:00 am
Location: London

Post by ShaneMuir »

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.
Post Reply