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


11

To reproduce the problem: SELECT *, (CASE WHEN IsGun=1 THEN CEILING(Price1Avg) ELSE Price1 END) FROM ( SELECT UPC, IsGun, Price1, AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg FROM ( VALUES ('A', 0, 14.99), ('B', 0, 29.99), ('C', 1, 319.00), ('D', 1, ...


6

It looks like your varbinary string got injected with some nonsense. All of the sequences of two 00 values are null characters, so this is why your string is terminating upon conversion. The first character is 0x24 (dollar sign), which is why the output is just a dollar sign. SELECT CONVERT(varchar(60), 0x2400....anything....); -- is equivalent to: SELECT ...


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


4

DATETIME only allows three places of millisecond precision. You'll either need to trim the trailing places beyond the first three milliseconds or if you're using any version of SQL Server 2008 or later you can use DATETIME2. DATETIME2 allows up to seven places of millisecond precision and the precision level can be explicitly set for your table or query. ...


3

There is too much precision in the varchar to be converted into datetime. One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this: declare @dt varchar(50) set @dt = '2015-12-02 20:40:37.8130000' select cast(@dt as datetime2(7)); If changing the column is not an option the you can do the ...


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

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.


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

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.


2

This returns the value you are looking for. I would strongly suggest if you have any control over this process that you stop storing data in this format. DECLARE @hexstr nvarchar(40) = '0x' + SUBSTRING(CONVERT(NVARCHAR(100), 0x008B010000000089FF32323633393933352D4B434E000000, 1),21, 24); declare @ind int, @byte1 int, @byte2 int, @binvalue varbinary(20) ...


2

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Nls_date_format parameter helps to implicitly convert the varchar to a date type. Example: SQL>INSERT INTO tbl values ('01-02-16'); SQL>SELECT ADD_MONTHS('01-02-16',2) In the above ...


1

Here is the sample query to convert the given varchar2 value to date and calculated the difference in hour. select (to_date('20160316124052', 'yyyy-mm-dd hh24:mi:ss')-to_date('20160316080002', 'yyyy-mm-dd hh24:mi:ss'))*24 DateAndTime from dual; Update SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 FROM LOGGING WHERE NAME='TERR_PAST' AND ...


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

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

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

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



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