Hot answers tagged

16

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


14

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.


13

Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a CHAR(6)? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a ...


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


10

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

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


8

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

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


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

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


6

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


6

Eithers of these 3 should work: SELECT color , HEX_1 = '#' + CONVERT(varchar(6), CAST(ABS(color) as varbinary(1)) + CAST(ABS(color/256) as varbinary(1)) + CAST(ABS(color/256/256) as varbinary(1)) , 2) , HEX_2 = '#'+ +CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2) ...


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

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

The return type of the FOR XML PATH expression is NVARCHAR(MAX), not XML. I was being confused by the fact that SSMS renders any column named XML_F52E2B61-18A1-11d1-B105-00805F49916B as if it has the XML data type. If I run the following command, I get a clickable XML result cell in SSMS: SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'); I was able ...


5

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.


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

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

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


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


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

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


4

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


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

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)


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

What version of SQL Server are you using? From SQL Server 2012 onwards you can use TRY_PARSE with its USING culture argument. You can also use PARSE, the difference being PARSE will fail if the conversion fails and TRY_PARSE will return a NULL, eg DECLARE @t TABLE ( x VARCHAR(10) ) INSERT INTO @t VALUES ( '7.000,45' ), ( 'xxx' ) SELECT x, ...


4

The biggest number you can fit into numeric(5,2) is 999.99. Therefore 123.556... is fine, but 1234.556... is too big. You can find the rules for truncation, rounding, and errors during implicit conversions in the documentation: CAST and CONVERT (Transact-SQL) I don't know SQL-server that well, so I don’t know if you can configure the behaviour. One idea ...


3

(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.) You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then ...



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