Clever Geek Handbook

Show date as "Dec 17, 2010" in CSV file generated via SSIS

I have a date column in a database table stored as "2014-12-17 15:16:00". I need to import this date along with several other fields into a CSV file with the format - "December 17, 2010". Is there a way to easily convert the date to this format in SSIS?

Through SQL, I can do it like below, but it doesn't return the full month name.
SELECT CONVERT (VARCHAR (20), GETDATE (), 107)
Can we get the full name of the month via SQL?

Also, if we go with the approach above, what kind of Datetype is used in the flat file connection manager for this date column to make it look great in the CSV file?

+3


source to share


2 answers 


Use the below approach to get full month name:

declare @d datetime ='2014-12-17 15:16:00'
    select datename(month,@d)+' '+cast(datepart(dd,@d) as varchar(20))+','+cast(datepart(year,@d) as varchar(4))


enter image description here

+2


source


The suggested Karthick gives the desired format in SQL Server.
But we will get the SSIS error below even if you use "string [DT_STR]" as the datatype in the package.
Column "MYDATE" cannot convert between unicode and non-unicode string datatypes

To handle this, we just need the CAST date for VARCHAR as shown below and the SSIS package works fine.



DECLARE @d DATETIME ='2014-12-17 15:16:00'

SELECT CAST (DATENAME (MONTH, @d) + '+ CAST (DATEPART (DD, @d) AS VARCHAR (20)) +', '+ CAST (DATEPART (YEAR, @d) AS VARCHAR (4)) AS VARCHAR (150))

0


source






More articles:


All Articles