April 12, 2023 at 9:40 pm
I have a big sql script that i am doing a flatfile destination on to create a csv. I have case statements and about 4 columns that I have used the FORMAT command on some dates, when I run the script in in SQL manager it all looks great and when i copy the script over to my SSIS package the output has the dates like mm-dd-yyyy 00:00:000 and my format line makes it mm/dd/yyyy 00:00:00. The format line and case statements are below. I am trying to figure out why the SSIS is not formatting them as I have done.
,FORMAT (BirthDate, 'MM/dd/yyyy hh:mm:ss') AS [BirthDate]
,FORMAT(HireDate, 'MM/dd/yyyy hh:mm:ss')AS [HireDate]
,[TermDate] = CASE WHEN FORMAT(Termdate, 'MM/dd/yyyy hh:mm:ss') > FORMAT(HireDate, 'MM/dd/yyyy hh:mm:ss')
THEN FORMAT(TermDate, 'MM/dd/yyy hh:mm:ss')
ELSE NULL END
April 12, 2023 at 9:56 pm
My first thought is that somewhere in the data flow there's a type conversion to datetime.
There's no other reason I can think.
Right click on the source component and choose advanced editor and look at the column types, be sure they are set to some character type column.
You can also check the metadata of the data flow line as it exits the source to confirm those columns are all character type also throw a data viewer on right after your source and see how the data looks.
April 12, 2023 at 10:26 pm
I have a big sql script that i am doing a flatfile destination on to create a csv. I have case statements and about 4 columns that I have used the FORMAT command on some dates, when I run the script in in SQL manager it all looks great and when i copy the script over to my SSIS package the output has the dates like mm-dd-yyyy 00:00:000 and my format line makes it mm/dd/yyyy 00:00:00. The format line and case statements are below. I am trying to figure out why the SSIS is not formatting them as I have done.
,FORMAT (BirthDate, 'MM/dd/yyyy hh:mm:ss') AS [BirthDate] ,FORMAT(HireDate, 'MM/dd/yyyy hh:mm:ss')AS [HireDate] ,[TermDate] = CASE WHEN FORMAT(Termdate, 'MM/dd/yyyy hh:mm:ss') > FORMAT(HireDate, 'MM/dd/yyyy hh:mm:ss') THEN FORMAT(TermDate, 'MM/dd/yyy hh:mm:ss') ELSE NULL END
first thing - avoid using format - it is slow compared to the alternatives (convert)
second DO NOT compare strings like that - if you are comparing dates you either compared them with datetime data types, or you use a format that can do the correct type of comparison.
on the case above, if someone was terminated on 12 January 2021 (01/12/2001 according to your format) but was hired on 12 February 1991 (02/12/1991) your compare means that the termdate is less than the hiredate and therefore outputs termdate as a null
regarding the file not being as you think it should be - what software are you using to verify the content? if not notepad (or a normal TEXT editor) then output is likely being formatted - this would be the case if you are using Excel to see the contents of the file.
April 13, 2023 at 8:20 am
[TermDate] = CASE WHEN FORMAT(Termdate, 'MM/dd/yyyy hh:mm:ss') > FORMAT(HireDate, 'MM/dd/yyyy hh:mm:ss')
THEN FORMAT(TermDate, 'MM/dd/yyy hh:mm:ss')
ELSE NULL
END
Is it intended that if the Termdate is 09 May 2023 and the HireDate is 12 December 2001 then NULL is returned?
As for the problem, I agree with TangoVictor here. I suspect that you've defined one of the columns in your dataflow task as a date and time data type; if it's treated as a DT_(W)STR the whole way through the dataflow then you wouldn't have that change unless you have something like a Derived Column Transformation to replace the slashes (/) with hypens (- ) and also concatenate an extra 0.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2023 at 1:10 pm
Thank you, i guess I had just looked over it.....it was set to dbtimestamp in the output.....thank you for that...
April 13, 2023 at 1:13 pm
If that is not the correct way to do dates then what is? I have used that way for a long time...but if you have a better way then by al means show me. because yes i have to compare two dates and if one is greater then use it or make it a NULL....so i am all ears to learn something. So teach me oh wise one.
April 13, 2023 at 1:15 pm
Yes i had to do it that way then vendor wants the dates in a specific format and that was the only way I knew how to achive that... and yes you thinking is correct if the Termdate is greater than the hiredate use it or populate a NULL
April 13, 2023 at 1:34 pm
Yes i had to do it that way then vendor wants the dates in a specific format and that was the only way I knew how to achive that... and yes you thinking is correct if the Termdate is greater than the hiredate use it or populate a NULL
Well, in my eyes, 12 May 2023 is greater than 09 December 2001, but it isn't in your logic '05/12/2023' is less than '12/09/2001'. Strings don't order in the same way as dates.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2023 at 1:48 pm
correct way to check dates is to use the date - FORMAT turns them to strings so only one format would allow for that compare (YYYY-MM-DD)
so use the following construct where the compare is done based on the source datatypes (datetime) and the format is using convert function - format could be used if volume is small - but test performance of both for your case.
select convert(char(11), t.BirthDate, 101) + convert(varchar(18), t.BirthDate, 108) as BirthDate
, convert(char(11), t.HireDate, 101) + convert(varchar(18), t.HireDate, 108) as HireDate
, convert(char(11), val.TermDate, 101) + convert(varchar(18), val.TermDate, 108) as TermDate
from (select convert(datetime, t1.HireDate) as HireDate
, convert(datetime, t1.TermDate) as TermDate
, convert(datetime, t1.BirthDate) as BirthDate
from (values ('2013-01-01', '2015-02-27', '1987-02-27')
, ('2015-01-01', '2013-02-27', '1987-02-27')
, ('2015-01-01', null, '1987-02-27')
) t1 (HireDate, TermDate, BirthDate)
) t
outer apply (select case when t.termdate > t.Hiredate then t.termdate else null end as TermDate
) val
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply