Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ugh3012
Yak Posting Veteran
62 Posts |
 Posted - 2013-07-15 : 13:45:08
|
| I have a SQL statement that takes the date format from 2011-03-01 00:00:00 to 03/01/2011. If I preview it in SSIS or run the DQL in MS SQL, it shows the correct date format. However, when I run it in SSIS to put the SQL result set to flat file, it puts it back to 2011-03-01 00:00:00. Why is it doing that? I have three tables and each have columns with date in it. All are working fine except for one. What am I missing? |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-15 : 14:09:15
|
| If I take out the field and replace it with this. Select '03/01/2011' As dateStrTest from ...it still returns 2011-03-01 00:00:00 in the flat file. I suspect it is the connection manger, but I am not sure what is causing that. |
 |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-07-15 : 16:19:00
|
| I found the problem. It was set to datetime in metadat, the area where you double click on the green arrow that is pointed to Flat File Destination. I could not figure out how to change it, so I deleted the OLE Source and put in a new one and this time I had the correct SQL syntax, so it recognized it as a string, not datetime. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 01:03:27
|
| Unless there's a business case, dont change date formats. Keeping them in native formats means you'll have less issues while using them for manipulations later. Otherwise you'll still need some logic to convert it back to datetime for comparison, sorting etc. This will have an issue especially when format is date is ambiguos.See below post for more detailshttp://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.htmlHowever if file is just send to user for display without any further manipulation, then you may be fine with it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
SSIS Date format