Tag Info

Hot answers tagged

15

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 ...


10

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);


8

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 , ...


8

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 cast operator: :: select (3.141593 :: bigint); You might also want to consider the various rounding functions.


6

Does MySQL have any support for custom data types? Simple answer: no Is there anything remotely like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of making the application call a function like the INET_ATON function. Oracle has CREATE TYPE which is analogous to some degree to a OO ...


5

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( ...


5

Microsoft reserves the right to change the binary representation of the build-in data types like DATETIME. So if you do an INSERT dbo.table(binaryColumn)VALUES(CAST(GETDATE() AS VARBINARY(8))); and then you upgrade SQL Server or even do an SP apply SELECT CAST(binaryColumn AS DATETIME) FROM dbo.table; might get you a different date then the one ...


5

From sql_variant (Transact-SQL) When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values. The base data type family for @v is Exact numeric and the base data type ...


5

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 ...


4

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)


4

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 ...


4

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.


4

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 ...


3

For your request: sorted by alphabets and then by numeric values I am assuming (deriving from your sample data), that only the first letter should be treated as text to sort by (easy to adapt). Further assuming that you want to sort by the first number in the string next (digits only). To break ties with trailing characters I finally order by the ...


3

Personally, I would buy the odbc driver and import all the files with the SQL wizard http://www.softvelocity.com/drivers/tsodbc.htm


3

I'd tend to export each table as CSV from Access, something it's quite good at. Then import it into PostgreSQL using COPY, possibly via a preprocessor like a Python script with psycopg2 if there's lots of mangled / invalid data. If there are lots of tables, VBA (Visual Basic for Applications) or similar can be used on the Access side to automate the export, ...


3

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 ...


3

Why don't you just use?: WHERE DateTime >= '2014-01-01' AND DateTime < CONVERT(CHAR(19), DATEADD(second, 1, GETDATE()), 126) Advantages: - No values in the varchar column will be converted so you'll get no errors - Efficiency as indexes can be used Disadvantages: - does not take care of timezone offsets - some of the values, while being ...


2

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 ...


2

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 ...


2

Create a new column (ALTER TABLE) then run an UPDATE on it UPDATE MyTable SET NewIntColumn = DATEDIFF(SECOND, '19000101', MyDateTimeColumn) 19000101 is the SQL Server epoch. You can use 19700101 for Unix epoch for example


2

There is a way. Given a table t and a function f() that returns an anonymous record that would match that table type: CREATE TABLE t (id int, d date); You cannot just cast the anonymous record, since a column definition list is required for SELECT * FROM f() Quoting the manual on the SELECT command: If the function has been defined as returning ...


2

Tough task. I would assume the worst and create: strings(x) as nvachar(x) Long as BigInt Dec(x,x) as Dec(x,x) After that you'll have to look at the data in each column individually and make decisions on your findings. Dates might be stored as strings. (MS is guilty of this as well in TypePerf.)


2

I think the best for your situation is to use the COPY ... TO with one file for each table, and use pg_dump only for schema. With COPY you can do change the output format (including NULL), example: COPY foo TO '/path/to/foo.output' WITH NULL '<your null>'; You can even use CSV or other format (I don't know what MSSQL recognizes). To automatize, ...


2

Try type casting to datetimeoffset type as below: CAST(datetime as DATETIMEOFFSET) For instance your query will become something like following: SELECT * FROM dbo.RebroadcastSmoothStreaming WHERE (CAST(datetime as DATETIMEOFFSET) BETWEEN CAST('2014-01-01T00:00:00-06:00' AS datetimeoffset) AND dateadd(day, 1, SYSDATETIMEOFFSET())) ORDER BY Ip


1

@MatheusOl provided a solution with COPY. I propose a couple of improvements: CREATE OR REPLACE FUNCTION f_copy_all() RETURNS void AS $func$ DECLARE _sql text; BEGIN FOR _sql IN SELECT format($$COPY %s WITH NULL '' TO '/path/to/%1$s'$$, c.oid::regclass) FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE ...


1

Another option would be to handle the data conversion within an SSIS package when loading the data. You could handle the value change back to NULL in process, then convert the data type back to what it should be.


1

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 ...


1

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] = ...



Only top voted, non community-wiki answers of a minimum length are eligible