November 22, 2012 at 10:28 am
Hi,
I am exporting a view to CSV, however 1 of the columns is a smalldatetime and i would like the csv to ONLY display DDMMYYYY - how can I achieve this?
November 22, 2012 at 2:29 pm
In the source query, use the following TSQL:
SELECT myDate = REPLACE(CONVERT(CHAR(10),mySmallDatetime,103),'/','')
FROM myView
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 23, 2012 at 3:25 am
Koen Verbeeck (11/22/2012)
In the source query, use the following TSQL:
SELECT myDate = REPLACE(CONVERT(CHAR(10),mySmallDatetime,103),'/','')
FROM myView
I added that to my create view statement (the ssis exports an entire view) and it works perfectly...
November 23, 2012 at 3:31 am
learning_sql (11/23/2012)
Koen Verbeeck (11/22/2012)
In the source query, use the following TSQL:
SELECT myDate = REPLACE(CONVERT(CHAR(10),mySmallDatetime,103),'/','')
FROM myView
I added that to my create view statement (the ssis exports an entire view) and it works perfectly...
Great, glad that it works and thanks for posting back.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 23, 2012 at 3:37 am
Koen Verbeeck (11/23/2012)
learning_sql (11/23/2012)
Koen Verbeeck (11/22/2012)
In the source query, use the following TSQL:
SELECT myDate = REPLACE(CONVERT(CHAR(10),mySmallDatetime,103),'/','')
FROM myView
I added that to my create view statement (the ssis exports an entire view) and it works perfectly...
Great, glad that it works and thanks for posting back.
I have a follow up question if you don't mind...
I can query the view and it displays the date perfectly...however the SSIS still doesn't like it...the flat file source still reads the column as datetime - I imagine the only solution is a data conversion task?!
November 23, 2012 at 3:54 am
I don't mind 🙂
You can change the datatype of the column in the Advanced Editor of the source component. Check the output column in the last tab.
Make sure you refresh the metadata in the dataflow. Go to the columns pane of the source component, uncheck the column and recheck it again. Hit OK.
Open up every component in the dataflow that has a red cross and fix possible metadata issues.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply