CODEWITHSUNDEEP
sql-servercsvssis
Ravi Shankar Kota
Ravi Shankar Kota

Reputation: 705

Show date as "December 17,2014" in CSV file generated through SSIS

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

Through SQL , I can do it as below but it is not returning the full month name.
SELECT CONVERT(VARCHAR(20),GETDATE(),107)
Can we retrieve the full month name through SQL?

Also If we go with above approach , what is the appropriate Datetype to use in Flat File Connection manager for this date column to appear fine in CSV file?

Upvotes: 3

Views: 205

Answers (2)

Ravi Shankar Kota
Ravi Shankar Kota

Reputation: 705

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

In order to handle this , we just need to CAST the date to VARCHAR as below and 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))

Upvotes: 0

knkarthick24
knkarthick24

Reputation: 3216

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

Upvotes: 2

Related Questions