SQL 2012 :: Converting Fahrenheit To Celsius
Jun 16, 2015I'm knew to Sql, I've tried many variations, and I keep getting errors:
CAST(ROUND(IP_BLa.MEAS_VALUE-32)*5/9,2)
CAST(ROUND(IP_BLa.MEAS_VALUE-32)*5/9,2)as decimal(3,2))
ADVERTISEMENT
SQL 2012 :: Second Delimiter And Converting
Nov 26, 2014I have a PL/pgSQL Code like this:
[code=" CREATE OR REPLACE FUNCTION public.split_string(text, text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE
pos int;
[Code] ....
Its split a String with a delimiter. Like this
[code="select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' ');
"3.584731"
"60.739211,3.590472"
"60.738030,3.592740"
"60.736220""][/code]
My question is how i can save the first result in a temp_array (or table i dont know) so I can get the result and split up the results again with the delimiter ','.
SQL 2012 :: Converting XML Files To PDF And Zip In Folder
Jun 16, 2015Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.
Can it be possible in SQL server BI world?
If possible make this an automated process for every 100 files.
SQL 2012 :: Converting Query To XML Result?
Jul 15, 2015I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
SQL 2012 :: Converting From Table With Varbinary To Filetable
Feb 17, 2014I have a very large database running in a production environment. The backup files are getting to be very difficult to manage. They are at 70gb as of now and growing at a rate of 10gb per month. This is due to document images being stored in the database in one table. I have read a little about a new feature in SQL Server 2012 called Filetable. Can we migrate to SQL Server 2012 and convert to the filetable structure, will this decrease the size of the backups? Would backup of the document images be taken care of by our nightly file system backups now?
View 1 Replies View RelatedSQL 2012 :: Converting Access Queries To Views
May 13, 2014Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
View 3 Replies View RelatedSQL Server 2012 :: Converting XPath To FLWOR
Jun 9, 2014I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />
[Code] ....
Here's the working XPath query and results:
select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)
Results:
Name
43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data
Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
SQL Server 2012 :: Converting Varchar To Datetime?
Mar 19, 2015I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies View RelatedSQL Server 2012 :: Converting Nvarchar To Datetime
Jul 24, 2015I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013'
"05/01/2014"...etc...
My requirement is to convert this column into Datetime ?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
SQL Server 2012 :: Differences In Rounding / Converting Strings?
Dec 9, 2013difference between
SELECT ROUND ('6.465',2) --- result 6.46
and
SELECT ROUND (6.465,2) --- result 6.47
with
It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...
Alright:
SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this:
SELECT ROUND('0.285',2) -- 0.28
SELECT ROUND(0.285,2) -- 0.29
SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.
MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:
* how is a string value exactly implicitly converted?
* how exactly does the estimation work, that in case of doubt rounds a value up or off?
SQL Server 2012 :: Conversion Failed When Converting Varchar Value
Dec 19, 2013Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
SQL Server 2012 :: Inconsistent Results When Converting To Time?
Jun 5, 2014I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
SQL 2012 :: Converting Directory File Path Into HierarchyID
Jul 22, 2014I am trying to create hierarchyID for a directory file path.
View 1 Replies View RelatedSQL Server 2012 :: Conversion Failed When Converting Date
Oct 30, 2014I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.
<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime
[code]....
SQL 2012 :: Save Real Datatype Without Converting To Exponential?
Sep 21, 2015I want to save 999999999 as real data in sql.but it saved 1+E09.
how can I save 999999999?
SQL 2012 :: Converting Case Statement To Config Table?
Nov 3, 2015I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE
WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
ELSE 0
END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
SQL Server 2012 :: Converting Year And Month Varchar To Date
Jan 31, 2014I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
SQL Server 2012 :: Converting Query Output To HTML Format
Mar 23, 2015I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
SQL Server 2012 :: Converting Large Excel Spreadsheet To Normalized Data
Aug 7, 2014I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.
Here is a small sample:
Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
The desired output:
Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696
and so on ......
The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.
SQL Server 2012 :: Conversion Failed When Converting Varchar Value To Data Type Int
Aug 12, 2014I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
R222209
R222220
R222222
R222212
R221123
F707768
[Code] .....
SQL Server 2012 :: Float Value Converting To Exponential While Inserting To Varchar Field?
Aug 6, 2015Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
SQL Server 2012 :: Moving Identical Data From One Table To Another - Error On Converting To Float
Jun 11, 2014So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.
Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,
[Code] ....
SQL Server 2012 :: Arithmetic Overflow Error Converting Varchar To Data Type Numeric
Oct 30, 2014OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.
Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))
SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?
Aug 14, 2014I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
SQL 2012 :: Converting Time String In Temp Table To Military Time Then Cast As Integer?
Dec 26, 2014I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
Integration Services :: 2012 Data Tools Crashing On Windows 2012 R2?
May 26, 2015SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).
When I try to open an existing project I get:
exception has been thrown by the target of an invocation
external component has thrown an exception (SSISUpgrade)
The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
SQL Data Tools page info:
Microsoft SQL Server Integration Services Designer
Version 11.0.2100.60
Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.5.51641 SP1Rel
SQL 2012 :: SSMS 2012 Database Connection Dialog Hangs
Feb 13, 2015I have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:
1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment)
2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect.
3. I can create a new virgin user on the PC and that login experiences the same problem.
4. A complete rip and re-install of SSMS 2012 does not resolve the issue.
Can I Easily SELECT A Date As October 12, 2012 (instead Of Oct 12 2012)?
Jan 29, 2008Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it!
View 4 Replies View RelatedConverting A Value
Nov 8, 2006I have a select statement like this:
select IsVerified from AppForm
the IsVerified returns 'True' ...can I convert that value to 'Yes' by using some sort of function?
Converting PL/SQL
Jun 9, 2005I have an anonymous PL/SQL block that I'd like to Convert into T-Sql and im completly stumped on 2 things:
What is the equvilent of a Cursor and how do I declare one?
How do I declare an anonymous block?
my code wrote:
Declare
-- Type Declarations
Type ct_UConstr_Cur is REF CURSOR;
v_Begin Integer := 0;
v_End Integer := 0;
v_PageSize Integer :=0;
c_Data ct_UConstr_Cur;
Begin
v_PageSize := :PageSize;
v_Begin := ( v_PageSize * :PageNumber) - 1;
v_End := v_Begin + v_PageSize;
Open c_Data For
Select * from (
Select A, B, C, ROWNUM as RN from Foo;
) where RN >= v_Begin AND RN < v_End;
:cur := c_Data;
END;
/
Converting Hex To Int
Sep 15, 1999Does any one know of a tool SQL 7 has for doing this kind of conversion?
View 2 Replies View RelatedHelp Converting .xls To SQL
Jun 29, 2007ok i am very very new at this, ive downloaded the SQL 2005 trial and i can not seem to figure this out. I have a .xls i want to make a active sql page out of. please please help me with this
the .xls is here http://www.mediafire.com/?5jtgzcxb232
feel free to email me @
bevanglynn@gmail.com
thanks
Converting (too Big)Raw Into Hex
May 25, 2007Hi there,
Here at my work(in Germany) i have a big problem:
Once a week there is a sqlscript, which should run and store data to a txt file.
There is a tablle in which imagedata is stored in RAW format(that one with small boxes, Qmarks and dots...).
Now i want to export it to the txtfile as HEX, so i can decode it by php further.
With the thumbnail, also stores there everything is fine, the SQL looks that way:
SELECt hex(thumbnail) FROM picTable where id_pic=54985
The Data is then displayed as HEX. But the standart picture is to big. When i use the statement:
SELECt hex(bigpic) FROM picTable where id_pic=54985
i got the error message:
An error occurred while running the query.
The data type, length, or value of argument 1 of HEX is invalid.
(SQL code = -171, SQL state = 42815)
After some research i found that the maximum lenght is 16 336 bytes for the HEX-operation. The picture is nearly 30 000 bytes... :shocked:
Have anyone an idea how to fix that?
I am not able here to accsess to the database direct...
I have been working for days on it.