More errors in convert function

In hijri calender any month can be 30 or 29 day
in convert function
if I write
select convert (datetime ,'29-12-1426,131) it is ok
this year 12 month is 30 days
but if I write
select convert (datetime ,'30-12-1426,131)
ther is an error

Thanks for any help

Tamer229,
There are many variations of the Islamic calendar. In some versions,
there are 29 days in the month Thou Alhajja in the year 1426, and in
others there are 30 days. There are only 29 days in this month
according to the Islamic calendar version used by Microsoft, which
is why you are getting an error when you try to convert the 30th day
of this month. In the Microsoft version, the western date January
30, 2006 is the first day of Muharram, 1427. In other versions of
the Islamic calendar, January 30, 2006 is the 30th day of Thou Alhajja,
1426.
See http://www.phys.uu.nl/~vgent/islam/islamyear_en.htm
for a calculator that shows several versions. The version used
by Microsoft is labeled "Ia [15, astronomical = “Kuwaiti algorithm”]"
Steve Kass
Drew University

month ('jan','feb',...) string to date conversion fails

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]]

Error: The component "Derived Column" failed because error code

0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

srem wrote:

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

Its not an ideal situation but if you want to achieve this in a single derived column component what you will need to do is build some nested conditional operators. Its a bit of a fraught process to begin with but once you get the hang of it it isn't too bad.

I hope Microsoft give us the ability to extend the expression language in the next version by allowing us to build our own custom expression functions.

-Jamie

|||I suppose you could build a quick lookup table using a simple stored procedure. Then all you'd have to do is join on that "Jan 02 2005" column to get you a real date field in return.

Might even work better than doing all of the CASTs and conditional logic tests.|||

If you know exactly what the month strngs are, and they are all 3 chars long, you could do something like the following to get month number:

(FINDSTRING(month, "JANFEBMARAPR....DEC",1) + 2 ) / 3

If there are multiple options, or the lengths are irregular you could still do something similar, but it might not be worth it anymore over the nested conditionals.