May 17, 2011 at 4:54 pm
need help with converting (yyyy-dd-mm to yyyy/dd/mm) using data conversion
May 17, 2011 at 5:40 pm
koolme_85 (5/17/2011)
need help with converting (yyyy-dd-mm to yyyy/dd/mm) using data conversion
Assuming that you want to go from a date to a char, you can do:
SELECT CONVERT(char(10), date_column, 111)
see http://msdn.microsoft.com/en-us/library/ms187928.aspx for details.
May 17, 2011 at 6:04 pm
i am pulling the date column from flat file which is in yyyy-dd-mm format , which i need to convert to yyyy/dd/mm
May 17, 2011 at 6:08 pm
Okay... what are you going to do with this once converted? It will store properly in one of the date data types as it is.
Have you looked into using the REPLACE function to replace the dashes with slashes?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 17, 2011 at 6:12 pm
no i have not , new to SSIS , would be gr8 if you explain me how its is done
May 17, 2011 at 6:19 pm
once converted , it will be stored in the records and they went it in the formate as mentioned above (in a table)
May 17, 2011 at 6:27 pm
Dates are probably one of the most confusing things to people working with SQL Server, so please forgive me if this sounds rather basic. It's one of those topics that we think should be obvious, but it's not.
What you're dealing with is an issue of storage versus display; if you're ever going to work with date data as a date (in other words, you want to know how many days passed since row X), then you want to store it as a date. How the data gets displayed is something else altogether; the display you see in the results in SQL Server Management Studio is affected by the settings on your machine as well as the server from which you retrieve the value. If you want to format the date a certain way in a SELECT statement, then use the CONVERT function I posted above to convert the column to a char value, and then format as you will.
In other words, you should probably do the following:
1. Import the date column from your flat file into SQL Server as a date (assuming you have SQL Server 2008 or better).
2. When you retrieve the data in a query, format the results using a CONVERT function.
May 17, 2011 at 6:37 pm
sorry for being opaque ...actually they want the work done using the data conversion transformation , so basically i have to use the flate file source and then use the data conversion transformation to change the format from yyyy-mm-dd to yyyy/mm/yy and get it into the table
May 17, 2011 at 7:44 pm
koolme_85 (5/17/2011)
sorry for being opaque ...actually they want the work done using the data conversion transformation , so basically i have to use the flate file source and then use the data conversion transformation to change the format from yyyy-mm-dd to yyyy/mm/yy and get it into the table
You need to go back to 'them' and figure out why they need the date formatted in a table. Dates are not formatted in the table and most likely the format they are requesting will not load correctly into a column with either a date or datetime data type.
The format you have will, however - load into a table with either a date or datetime data type as is.
If the column you are putting this data into is not defined as either a date or datetime data type - follow up with them to find out why they are not using the appropriate data type.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply