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


10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


7

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) +CONVERT(...


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


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


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


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


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

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

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

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

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

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

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


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

Error is at below select statements. If you do ALT+F1 on sys.confiugrations you'll notice that value_in_use column is of type sql_variant and you are trying to assign it to int variables. To fix this, use below statements in your script and it should work fine. SELECT @advanced_options = cast(value_in_use as int) FROM sys.configurations WHERE name = 'show ...


1

Basically, you need a window function instead of the aggregation. Appending an OVER clause to an aggregate function makes it a window-aggregate function: SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000, 1 FROM time_raw GROUP BY "time"; And since you want the minimum of the whole table, it's OVER () (nothing between the ...


1

I normally put the original values in temp table and then once done with the script/task, I revert it back. SET NOCOUNT ON; IF OBJECT_ID('tempdb..#xp_cmdshelloption') IS NOT NULL DROP TABLE #xp_cmdshelloption; CREATE TABLE #xp_cmdshelloption ( configuration_id INT ,NAME NVARCHAR(35) ,original_value SQL_VARIANT ) INSERT INTO #...


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


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

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



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