RFE: SSIS should recognise YYYYMMDD HH:MI:SS & YYYY-MM-DDTHH:MI:SS

Hi,

I've found a bit of an inconsistency between the way SQL Server treats dates and the way that SSIS treats them.

As stated here: http://blogs.conchango.com/jamiethomson/archive/2005/04/26/1337.aspx the only totally unambiguous datettime string formats that SQL Server accepts are:

yyyymmdd hh:mi:ss

yyyy-mm-ddThh:mi:ss

Given that then, its a bit annoying that, as stated here: http://blogs.conchango.com/jamiethomson/archive/2006/06/26/4141.aspx those two formats are not accepted as valid datetime string formats in the SSIS pipeline (check the bottom data viewer in the screenshot at the above link).

I know that there is no strict requirement for SQL Server and SSIS to do the same thing as each other in situations like these but let's be honest - we all expect it to.

Any comments from MSFT?

Is it worth me raising this at Microsoft Connect?

-Jamie

Jamie,

For those counter examples in your second link, have you tried using a data conversion transform rather than derived column, and setting the FastParse property on the new columns to True? Those formats look like ISO standards, which should be correctly converted by the FastParse routines.

Mark

|||

Mark,

I haven't tried that, no. But in suggesting it you've kinda made my point for me.

i.e. If they are ISO standards, why doesn't SSIS expression language like them?

-Jamie

P.S. FastParse isn't relevant here cos this isn't data from a flat file source.

|||

Anyone?

cheers

-Jamie

|||

I wrote a really nice answer to this and lost my connection just as I was posting. :-( So here's a shorter version.

We use the oleaut*.dll libraries for standard conversions to be compatible with as wide a range of technologies as possible. It is important to remember that we talk to SQL through clients - so compatibility with the client interfaces is the critical factor. Some clients such as the OLEDB and ADO.Net Providers can do limited conversions themselves, or pass, say, string data to the engine for conversion on loading. This latter technique is useful if you want to use the engine's own conversions - although they are limited, and you don't find errors until trying to load them.

SQL Server has their own routines, but also includes sps for conversion to ole formats. See http://msdn2.microsoft.com/en-us/library/ms191530.aspx

We do not extend the oleaut* conversion routines in our standard conversions and parsing - we do so by implementing additional functionlity in FastParse.

Fast Parse is not just for text files - you can find it on the advanced tab of the Data Conversion component too.

Great points Jamie - thanks for this thread.

Donald

|||

Donald Farmer wrote:

Fast Parse is not just for text files - you can find it on the advanced tab of the Data Conversion component too.

Doh! Well I learnt something then!!!

Thanks Donald!!

-Jamie

Returning Dates not in the SQL Server DB Table

Hi,

I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent.

Any help will be greatly appreciated,

Thanks,

Najla.

We need to see the structure of your table and possibly some data and expected output to understand what you are asking.

|||

The attendance table has columns:

emp_id attend_time attend_date attend_status

As an example, if an employee ABC with emp_id = 40 marks his attendance, the current date and time is automatically recorded. Also attend_status is marked as 'IN'.

I want to check attendance of an employee from 01/11/2007 to 06/11/2007.Consider an employee who was absent on 2,4,5 of Nov. Only his attendance of dates 1,3,6 would have been entered. So I can easily return his dates by :

select attend_date where emp_id=40

But what i want to ask is how can i return his absent dates which havent been entered in the table?

It definitely requires some C# coding. Somehow if i can get hold of the data and pass it through a loop, and compare it with some calendar, then i maybe able to do it.

Any suggestions?

Thanks,

Najla

|||

You can do it in C# also by using some logic, but if you want to achieve this in SQL that can be done with the below approach.

You can do it by having a separate table for all working dates for your office. No matter any employee was present or absent an entry for one working day will be there in your table. Then you can use this table with Left Join to the employee attendance table and you will get both the present and absent dates for any employee in a single select. The structure of the table can be like:

create table WorkingDays( rowIdidentity ( 1 , 1 ) , workingDatedatetime , totalEmployeesPresentint , totalEmployeesAbsentint)

The total present and absent employees are the 2 extra fields in this table, which I suppose you can use to store the extra information for a working day. Now, You can populate these records each day just after the attendance work is finished and utilize this table for finding out any employee's status on a particular day. The employee status according to your above requirement can be found out using the below query:

select w.workingdate , a.emp_idfrom workingDays wleftjoin attendanceon a.attend_date = w.workingDateand a.empId = 40where w.workindDatebetween'01/11/2007'and'06/11/2007'

You'll get emp_id null for the working days on which the employee was absent.

|||

OK thanks Dhimant, i get the approach and i think it can be easily done, but can anyone give me another idea without having to create a new table?

What i was thinking was to use a dataset and then comparing the values 1 by 1 to an array containing the dates b/w 1/11/2007 and 6/11/2007.
Is the approach feasible? How can I get values from the dataset and compare with the ones in the array?

Thanks,

Najla

|||

SELECT DISTINCT attend_date

FROM MyTable

EXCEPT

SELECT attend_date

FROM MyTable

WHERE emp_id=40

or

SELECT DISTINCT attend_date

FROM MyTable t1

WHERE attend_date NOT EXISTS(SELECT attend_date FROM MyTable t2 WHERE t1.attend_date=t2.attend_date AND t2.emp_id=40)

These however, make the assumption that someone else attended that day. Any day that noone attended isn't caught.