truncation of datetime works fine in tsql but does not work when data loaded to txt file

Question

Post reply

truncation of datetime works fine in tsql but does not work when data loaded to txt file

komal145

SSCrazy Eights

Points: 9915
More actions
July 25, 2016 at 2:05 pm

#328499

I am trying to pull date format yyyy-mm-dd hh.mm.ss from '2016-07-05 02:38:52.1900000 -04:00'
my sql:
CONVERT(VARCHAR(19),datecolumn,120)
this returns :2016-07-05 02:38:52.
But when i load this column to my text file in ssis package from oledb to flatfile. it loads like this:
2016-07-05 02:38:52.0000000 +00:00.
Any reason why it behaves like this, can anyone tell me how to fix it?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Quick question, what is the output data type?

😎

This task is trivial and a simple conversion to SMALLDATETIME should do the trick

DECLARE @dt DATETIME = GETDATE();

SELECT

@dt

,CONVERT(SMALLDATETIME,@DT,0)

;

komal145 SSCrazy Eights Points: 9915 More actions · Answer 2

My query column coversion to varchar :

CONVERT(varchar(19),[Date],0) =2016-07-05 02:38:52

Before conversion date column value in table: 2016-07-05 02:38:52.1900000 -04:00

Since there is change in flat file columns, i changed datatype on flatfile task -> connection manager->column ->column datatype ->dbdate

Still not working

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

komal145 (7/27/2016)
My query column coversion to varchar :
CONVERT(varchar(19),[Date],0) =2016-07-05 02:38:52
Before conversion date column value in table: 2016-07-05 02:38:52.1900000 -04:00
Since there is change in flat file columns, i changed datatype on flatfile task -> connection manager->column ->column datatype ->dbdate
Still not working

Use the correct style parameter with the convert function

😎

komal145 SSCrazy Eights Points: 9915 More actions · Answer 4

I used Convert(varchar(19) , [Date] ,120 )

still i get the same in text file with datetimeoffset

komal145 SSCrazy Eights Points: 9915 More actions · Answer 5

here is the ccolumn date output in text file :2016-07-05 02:38:52.1900000 +00:00

The column from table is of type datetimeoffset

truncation of timezone apprears in tsql but not when loaded to test file.

Need the format 2016-07-05 02:38:52 in text file too.