Date Problems

Kel · Posted: Mon Jun 29, 2015 2:25 am

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!

ShaneMuir · Posted: Mon Jun 29, 2015 3:06 am

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 · Posted: Mon Jun 29, 2015 3:38 am

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.

priyadarshikunal · Posted: Mon Jun 29, 2015 3:51 am

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.

Kel · Posted: Mon Jun 29, 2015 3:57 am

Hi Priyadarshi,

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

ShaneMuir · Posted: Mon Jun 29, 2015 4:30 am

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.