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.

Money conversion

Hi!

When I write:

'SELECT Amount FROM tTransaktion'

I get returnvalues such as '12000.0000'. Instead, I want it to return '12 000'.

The Amount column is of datatype money. Is this possible!?

Thanks!

select amount, amt, left(amt, len(amt) - 3) as [What you want]
from
(
select amount, replace(convert(varchar(20), amount, 1), ',', ' ') as amt
from
(
select convert(money, 12000) as amount
) a
) b|||

Sure, it's possible using the STR function.

declare @.value money

set @.value = 12000.00

select str(@.value,8,0)

The question is why? It is best to let the user interface handle the display of values and just send back raw values. Also, the money datatype has some issues with roundoff that is covered here: http://www.aspfaq.com/show.asp?id=2503

|||Louis - you're absolutely right -> stupid of me not to let the user interface handle it. Thanks for your answers!|||

Stupid, nah! I still have to fight myself to not format data in SQL since I have control over it and I am addicted to SQL :)

The UI programmers like it too since it is easier on them, but they are also the ones that argue about scalability of the database server, so offloading CPU work like this to them is easier to justify. Plus, you can use the regional settings of the client to display the data as they desire (which is a good thing too.)

|||

If you need commas you can use this

declare @.value money

set @.value = 12000.00

select convert(varchar,@.value,1)

Denis the SQL Menace

http://sqlservercode.blogspot.com/