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!
Date Problems
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 31
- Joined: Mon May 11, 2015 3:20 am
- Location: Robinsons Cybergate Tower 2
- Contact:
Date Problems
BOG
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.
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.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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.
Genius may have its limitations, but stupidity is not thus handicapped.
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?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.
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.