MySQL, how I hate thee edition 51984897

Coworker asked me to take a look at a query that was behaving oddly.
Ordering by a activationdate and then value, yes, the actual column
name is `value`. No, the ordering was working correctly but the data
type for Value is varchar(20) and it holds decimal data. Yes, that's
one of many sins in the flat file data holding system because it
certainly isn't an RDBMS. "Oh, you'll just need to conver that to a
floating point type. Should be 'cast(`Value` as decimal(10,2) as
Value'"

Heh-heh, if only that were the case. Have I ever mentioned that we're
on an ancient-ass version of MySQL---4 and some change. A few minutes
of the infuriating "You have an error in your SQL syntax. Check the
manual that corresponds to your MySQL server version for the right
syntax to use near..." Check the manuals, yes, that should be it. Go
go google power and hey, look at that. Converting a string to number
(decimal or int) isn't supported.

Well, it's not supported by the CAST operation. But if you round it,
then MySQL will be happy to convert a string to number-based datatype.
Thus ''round(`Value`, 2) as Value'

/facepalm

SSIS file and date expressions

While others have undoubtedly blogged about expressions, this post will cover the ones that I seem to be dealing with most frequently.  What is not covered is yyyymmdd or ccyymmdd to a valid date format ever since I discovered the fast parse option (TODO:  cite)

 

One thing I have found is that given the lack of real estate in the expression editor, it is helpful to have lots of variables doing small tasks vs writing the one big epxression.  For example, the expression to rewrite a file name with a datestamp in it, has 4 variables:  base file name, extension, date stamp and the rewritten file name

 

Expression to strip the file extension from a file name

SUBSTRING(@[User::CurrentFile],1, (LEN(@[User::CurrentFile]) -FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1)))

 

Expression to get the file extension from a file name

SUBSTRING(@[User::CurrentFile], (1 + LEN(@[User::CurrentFile]) - FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1)),len(@[User::CurrentFile]))

 

Date string (yyyymmdd)

(DT_WSTR, 4) year(@[System::StartTime]) + right("0"+ (DT_WSTR, 2) month(@[System::StartTime]), 2) + right("0" + (DT_WSTR, 2) day(@[System::StartTime]), 2)

Datetime string (yyyymmddhhmmss)

(DT_WSTR, 4)  year(@[System::StartTime]) +right("0"+ (DT_WSTR, 2) month(@[System::StartTime]), 2) + right("0" + (DT_WSTR, 2) day(@[System::StartTime]), 2) + "TODO:  time portion"

Insert a date string before the final period

SUBSTRING(@[User::CurrentFile],1, (LEN(@[User::CurrentFile]) -FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1))) + "." + (DT_WSTR, 4) year(@[System::StartTime]) + right("0"+ (DT_WSTR, 2) month(@[System::StartTime]), 2) + right("0" + (DT_WSTR, 2) day(@[System::StartTime]), 2) + SUBSTRING(@[User::CurrentFile], (1 + LEN(@[User::CurrentFile]) - FINDSTRING(REVERSE(@[User::CurrentFile]), ".",1)),len(@[User::CurrentFile]))