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


9

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.


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


7

Data Flow Here's the general approach I'd take to solving your problem. I started with your source data and added some other conditions - a NULL as well as a 14 and 15 year to ensure my logic later is correct. SELECT D.DrvDOB FROM ( VALUES ('470324') , ('470324') , ('470209') , ('140209') , ('150209') , ('101') , ('0') , ...


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

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

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

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


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


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


4

By default, as documented in MSDN, if no length is specified for varchar it will default to 30 when using CAST or CONVERT and will default to 1 when declared as a variable. To demonstrate, try this : DECLARE @WithLength varchar(3),@WithoutLength varchar; SET @WithLength = '123'; SET @WithoutLength = '123'; SELECT @WithLength,@WithoutLength This is very ...


4

This type of formatting is generally best done in your application if possible. The problem is that the case expression returns a result based on the highest datatype precedence of any branch. So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar. Also you should probably add year into your ...


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


3

Since BETWEEN is very problematic due to rounding of different date/time types and other problems, and since YYYY-MM-DD is not a safe format without the awkward T, an open-ended range using ISO standard full dates with no separators is a much better approach: WHERE Created >= '20141101' AND Created < '20141201';


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

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

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

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



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