Tag Info

Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding POWER() here. Hold on to your knickers. Preamble First, I present to you exhibit A, the MSDN documentation for POWER(): Syntax POWER ( float_expression , y ) Arguments float_expression Is an expression of type float or of a type that can ...

It seems that despite the implication in BOL that the left hand operand will be implicitly cast to float that this is not the case. The output of POWER() is cast to the type of the left hand operand, which is DECIMAL if you use 10.0. Using an explicit float works fine. SELECT POWER(1e1, 38); SELECT POWER(CAST(10 as float), 38.0);

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query. Usage In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length. Instead of SELECT E.BusinessEntityID , ...

You could also add this as a computed column or make it part of a view, so that you don't have to perform the calculation over and over in every query. As a computed column: ALTER TABLE dbo.whatever ADD dt AS CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'), 120); Since the calculation is deterministic, it can be persisted and/or ...

Can you be more specific on "quite large"? In general you're right, you shouldn't just 'do it live' in production with any change. Do you have any kind of QA or test environment you can do a run on first? This isn't the most sexy ninja one line approach but you could Add a new bit column Issue updates setting the new column = if( ...

There are two ways to typecast in Postgres: You either do it the SQL standard way: "select cast(3.141593 as bigint);", or you could use the Postgres-specific "::"-syntax: "select (3.141593 :: bigint);" You might also want to consider the various rounding functions.

Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime. Something like this: declare @S varchar(50) set @S = '20120606122012' select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)

No, there's no magic or hand-waving here. It'd be great if synonyms, say, applied to types, but that is not the case. If you want to make these columns first-class citizens, you'll need to change the table. You can automate this to some degree, though I won't post code to help with this unless you specify what you mean exactly by "manually" and why you think ...

As I understand it now, you need the entity framework to see the table as having a NOT NULL BINARY_DOUBLE, but the data needs to be/is stored in a NOT NULL NUMBER. This is a problem when using a view because the view does not pass the NOT NULL attribute through when it contains a CAST. Your options include the two Alex Poole +1 mentioned on SO (1. Virtual ...

Natively, there is no way to do that. But you can download - Replacing Data Conversion Component for SSIS from Codeplex and do that in one shot. More info can be found here.

I have an interesting idea. How about letting mysql decide the best datatype ??? Here is an example using PROCEDURE ANALYSE() Also, I have some sample data of lengths 0,10, and 16 use test drop table if exists worktable; create table worktable ( id int not null auto_increment, chdata varchar(16), primary key (id) ) ENGINE=MyISAM; insert into ...

According to the SQL Standard, numeric values may only be compared with numeric values e.g. an INTEGER value can be compared with a NUMERIC value by coercing both to NUMERIC values. It sounds like MySQL is not compliant with Standards in this regard. To be fair, most SQL product exhibit similar implicit type coercions and it is encumbent on users to avoid ...

You can add a new column and manually update it as @gbn suggested, but now you have to constantly keep this column up to date with insert/update triggers or some other mechanism. Borrowing @gbn's guesses on table/column names, here are a couple of different approaches that don't require constant maintenance. Computed Column ALTER TABLE dbo.MyTable ADD ...

From New "Type Conversion in Expression....." warning in SQL2012 ,to noisy to practical use I see what you mean. While I agree that this is noise in most cases, it is low priority for us to fix. We will look at it if we get more feedback. For now I have closed this by design. I will provide the boring answer until someone comes along with a better ...

I tried your script and recreated your environment completely and i didn't see any error. Ran perfectly and created all tables and transferred all data successfully i tried running under a sql agent job and still no error was generated but i guess you can update your insert script by making sure the column gets converted under all conditions [BO_ID] = ...

You can't handle SQL exceptions within an SQL statement like you can from within PL/pgSQL. You must write a custom function as you suggest, for example: create function is_valid_date(text) returns boolean language plpgsql immutable as $$ begin return case when $1::date is null then false else true end; exception when others then return false; end;$$; ...

It seems that at the moment you don't have and index on that column then I think your first considered approach is quite good but instead of changing current column values and then changing its type instead create a new column with bit data type and set its values checking the length or if its same string then by exact match after that delete the old column. ...