ETL and Data Integration tools - Modern ETL software for everyone

Good morning,

I can't seem to figure this one out....

I have a package that I run in VIP which reads/outputs/updates sql tables - everything is in MS sql.
Therefore I expect the dates to be in the format YYYY-MM-DD correct?

This is true in most cases, but in some situations I output the table, as YYYY-MM-DD in SQL and when I read it back into the next process in VIP and it shows as MM/DD/YYYY ....therefore I need to adjust the date format in VIP for it. I tried it on another system and it showed as DD/MM/YYYY

Is there a setting somewhere I'm missing?....
thanks
Joe

Joe

I think it might have something to do with system date format

Mike

Thanks Mike....I tried changing the date format in Windows regional settings and it does indeed change the format in VIP - which is not a good thing at least for me. It means the script will behave differently depending on the windows settings vs the sql field. Not sure if there is a solution for this in VIP.

Two other things I found in testing this:

1) SQL 2008 has a new format called "date" which stores the date only (no time). I believe VIP sees this as a character field which would explain why it shows as YYYY-MM-DD (and not DD/MM/YYYY) when I initially import it. I have tried to use the "date" field as an output in VIP and I consistently get an error - the date/time appears to be truncated. I can't find any way to get around it and I am starting to see this data type being used more often.

2) There is no drop down for YYYY-MM-DD in the mapping area....can one be added in the next release (or can I control this somehow)?

Thanks

Joe

Joe

We might force the format for date fields 'YYYY-MM-DD HH:NN:SS.ZZZ'.
We are currently retesting import routines and fixed a lot of bugs we are not yet ready for the release
Issue 1 and 2 will checked as well
I will let you know when it is ready

Mike

Joe

We have added format YYYY-MM-DD for you

Also the default format for odbc and ms sql now is YYYY-MM-DD HH:MM:SS.FFF

Mike