Hot answers tagged

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


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

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.


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

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


3

A smallint in SQL Server is stored as a signed int16, or 2 bytes, or 16 bits with the 16th bit reserved to indicate the sign (0 = positive, 1 = negative). Here are your two examples, converted to the original un-signed int (uint), and int16 values: It looks like in your examples that your bits have been shifted to the left by 8 bits. Noticed the bolded ...


2

Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts: TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0) MSDN: TIMEFROMPARTS (Transact-SQL) With SQL Server prior to 2012, you can use such queries: ...


2

How are your underlying item prices stored? If, for example, your items are all priced/stored in EUR, then there is no need for an NxN (duplicative) currency matrix. In this case, exchange_rate (against EUR or your base currency) can simply be a field in the currency table (vs an independent table). Then all of your conversions for display (which are ...


2

As you mention you string value format is in 21/11/2105 then you can try following one Alter table table name alter column column name datetime NULL ( null is optional if you want to allow null value to your field). Note that if the column contain string value like "abc" (other than date format value) then it will show error as conversion fail.


1

in 5.6.12: (Well, not really an answer, but more info.) mysql> insert into se108833 (product_id,product_id_nn) values ('',''); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message ...


1

As you can see from the definition you provided for [dbo].[ConfigData], both Value and ValueInUse are defined as SQL_VARIANT data types. The error message you are receiving is: Msg 257, Level 16, State 3, Procedure usp_SysConfigReport1, Line 19 Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this ...


1

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar). In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE ...


1

db.getCollection('test').find().forEach(function (doc) { db.getCollection('test').remove({ _id : doc._id}); tempId = new NumberLong(doc._id); doc._id = tempId; db.getCollection('test').save(doc); } ); This will do. You may want to add error handling as per your use case. Also make sure you have backup before migrating.


1

Should do it: SELECT a, b FROM unnest(ARRAY[(1,varchar 'hello'), (3,varchar 'world')]) AS t(a integer, b varchar(255));


1

I think you might be mistaken about what happened to your data. Positive integers generally have the same representation as signed and unsigned integers. It looks like your first value is 564 or 141 * 4. The second value looks like 424, or (362 - 256) * 4. It's possible that your integer was stored as a byte (truncating it to 0-255) and then bit-shifted, to ...


1

Dates in Clarion TPS files are stored as a Clarion Long, which is an integer. They are defined as the # of days elapsed since December 28, 1800. The valid Clarion Standard Date range is January 1, 1801 through December 31, 9999 February 27, 2015 - the date Nimoy died - is Clarion date 78,223. A Clarion String is a CHAR. A Clarion Cstring is a VARCHAR - ...


1

I was close, unlike JavaScript and PHP it seems PostgreSQL array key numbers start with 1, not 0. UPDATE parts SET material_size_temp[1] = material_size;


1

For anybody who may come across this problem, I solved using this function: -- Description: Converts ARGB to RGB(RR,GG,BB) -- e.g. 16744703 returns RGB(255,128,255) or #FF80FF CREATE FUNCTION [dbo].[ARGB2RGB] (@ARGB AS BIGINT,@ColorType AS VARCHAR(1)) RETURNS VARCHAR(16) AS BEGIN DECLARE @Octet1 TINYINT DECLARE @Octet2 TINYINT ...



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