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


11

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.


11

In SQL Server 2008, converting binary to a character representation became a lot faster and easier: CREATE TABLE dbo.X ( pk integer PRIMARY KEY, c1 integer NOT NULL, rv rowversion NOT NULL, rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1) ); Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to ...


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


7

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


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


5

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


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


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


5

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


5

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


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

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

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


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

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. ...


4

It is ugly, but you can try: SELECT a, b::text FROM unnest(ARRAY[(1,'hello'), (3,'world')]) AS t(a integer, b unknown); This way the type defined in AS matches the output of unnest(), which you can cast to your needs in the SELECT list. You can try this in a small SQLFiddle.


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

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


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

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

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format. Try this: select count(*) from dbo.profile where [created] between ...


3

You can do this without generating a warning by creating a type and casting the records to it: create type t as (a integer, b varchar(255)); select * from unnest(array[(1,'hello'), (3,'world')]::t[]); ┌───┬───────┐ │ a │ b │ ├───┼───────┤ │ 1 │ hello │ │ 3 │ world │ └───┴───────┘ tested on 9.4 and 9.3 (SQLFiddle here)


2

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


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



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