SQL Server 2014 :: Convert Decimal To Int In Case Condition

Apr 22, 2014

I have a table exam_setup

CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),

[Code] ....

I have to fetch the values like

assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?

[Code] ....

View 3 Replies


ADVERTISEMENT

SQL Server 2014 :: CASE Condition Not Affecting CAST Output

Jun 10, 2014

I've a variable in my SP that I'd like to use to change the format of my output. Try this!

select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Then this:

select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end

Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):

select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2

Work fine.

View 8 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

SQL Server 2014 :: Need A Query On Group By Condition

Oct 11, 2013

I have one table which contines 3 columns they are

1.emp
2.monthyear
3.amount

in that table i am having 6 rows like below.

emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000

i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.

out put should be like this
1 2000
2 1000

View 9 Replies View Related

SQL Server 2014 :: Row Count Condition Check

Jun 12, 2014

I have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. condition check part of the code?

use msdb
go
create table ##resultset (
total int)

[code]...

View 5 Replies View Related

SQL Server 2014 :: Error In Adding Condition In Query

Apr 4, 2015

Following is a working code

declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')

[Code] ...

I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id

I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'

When I tried this code I am getting error.

View 3 Replies View Related

SQL Server 2014 :: Converting Hour And Minute To Decimal?

Jul 31, 2014

I am having some issues converting time to decimal from one of my column which display it as

2014-07- 08:16:31.000 (datetimecreated)

View 9 Replies View Related

SQL Server Admin 2014 :: No Objects Listed For Server Performance Condition Alert

Jan 25, 2015

We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".

View 3 Replies View Related

SQL Server 2014 :: How To Remove Trailing Zeros From A Decimal (type) Value

Mar 16, 2014

I would like to 'drop' some trailing zeros from a decimal value, e.g.: 50.000000, and I am wondering how to go about this?

The value is definitely of decimal type, and in this instance I know that I want to eliminate exactly six (6) zeros.

View 4 Replies View Related

SQL Server 2014 :: Finding NULL In CASE Statement

Sep 15, 2015

The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,

CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'

[Code] .....

View 9 Replies View Related

SQL Server 2014 :: COALESCE To Replace Multiple CASE Statements

May 27, 2014

I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.

Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().

SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A

View 9 Replies View Related

Using Case In Where Condition In SP

Sep 28, 2013

I have a SP with where condition

as where id=@id and month=@month and Eid=@Eid

What I need is if @Eid=0 i need to display all the records

if Eid>0 then display only that particular reocrd

I tried

where id=@id and month=@month and Eid=
case when @Eid=0 then Eid>0
else
Eid=@Eid
End

But its giving me error

Incorrect syntax near '>'.

View 3 Replies View Related

Case When With More Than One Condition

Aug 21, 2014

Is it possible to have more than one conditions per line? Example below. It works fine if I do this, "When 'M" then 'Yes' But, I am trying to have it to check for more than one. The below example does not work. What can I do to do something like this.

SELECT
FirstName, LastName,
CASE WorkCode
WHEN 'M', 'E', 'F' THEN 'Yes'
WHEN 'C', 'D' THEN 'No'
END
FROM Employees

View 5 Replies View Related

SQL Server 2014 :: Function To Convert Datetime To String

Jan 26, 2014

Writing a SQL Function as below

the input parameter for function should be datetime of sql datetimeformat

and out put should be a string = yyyymmdd1 or yyyymmdd2

The last character 1 or 2 based on below condition

if time is between 6AM and 5.59PM then 1
if 6PM to 5.59AM then 2

View 6 Replies View Related

SQL Server 2014 :: Convert / Insert All Files In A Directory

Feb 24, 2015

My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7.

DROP TABLE images
CREATE TABLE images
(
image_name varchar(500) null
,image_data varbinary(max) null

[Code] ....

View 2 Replies View Related

SQL Server 2014 :: Case Sensitive Collation Causing Cardinality Warning

May 18, 2015

Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.

Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).

/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,

[code]....

View 8 Replies View Related

Sql Server 2005 Auto Convert Text To UPPER Case

Jul 13, 2006

Our sql server 2005 database is receiving data from a third part program over which we have no control. We need to be able to automatically convert data entered in one column of one table to UPPER case only.

How can this be done in the table itself?

View 3 Replies View Related

SQL Server 2014 :: Case Syntax In Store Procedure Evaluating 2 Query Options

May 4, 2015

What will be the best way to write this code? I have the following code:

BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal

[Code] ...

then if Signal Sent.... do the following query:

Select * from package.....

if signal not sent don't do anything

How to evaluate the first query with the 2 options and based on that options write the next query.

View 2 Replies View Related

SQL Server 2014 :: Convert 6 Digit Letter To Month And Year

Feb 27, 2015

How to convert 6 digit number to mm/yyyy.

View 5 Replies View Related

Can I Use CASE Expression In AND Condition?

Jul 8, 2005

Hello:Is it possible to use CASE expression in AND condition? i.e.------------------------------------------CREATE PROC spBlah(   @id INT,   @val INT)ASSELECT * FROM aTableWHERE tableID = @idAND   (      CASE @val WHEN 1 THEN otherCol = someValue END      CASE @val WHEN 2 THEN otherCol != someOtherVlaue END   )-------------------------------------------

View 2 Replies View Related

Using Case To Subtract To Condition

Sep 30, 2013

I have data for moths which are commulative, for example Feb where month is 02, ecul Jan. + Feb. data. I would like to have the data for only Feb = when month '02' Feb = data for '02' - data for '01'. I would like to writ these commands: For Jan there is no problem to write the command but for other months I am not sure how to write the commands for months other than Jan.

,Jan_qty_1 = case
when [stat_month] = '01' then cast(sum(cast([qty_1_mo] as bigint)) as bigint)
else CAST('0' as int)
end

[Code] ....

here is the data:

ust_code district stat_year stat_monthqty_1_mo
24105220070112619
2410522007022200
2410522007024438
2410522007030
24105220070314434
2410522007042295
2410522007040
2410522007050
2410522007050
241052200706454

View 3 Replies View Related

SQL Server 2014 :: Separate Words To Rows (Convert Comma Separated)

May 3, 2015

I have a table Sample with data stored like below

ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL

I need the output like..

-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------

How can I do the same with a select query in SQL Server?

View 4 Replies View Related

SQL Server Admin 2014 :: How To Convert Float To Timestamp In Single Select Query

Mar 18, 2015

How to convert float to timestamp in single select query

E.g., i have float as 1.251152515236 ,

I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00

View 1 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test 

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

Cannot Convert To Decimal

Apr 17, 2007

I have a money field in SQL that when i try and get the sum of it i cannot convert it to decimal.  This was working now its not, and nothing was changed.Any reason for the error?    DECLARE @TEST decimal(10,2)SET @Test = (SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID='0000010604')Print @TestRETURNS: 160471.24----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Specified cast is not valid. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.InvalidCastException: Specified cast is not valid.Source
Error:



Line 3576: // CURRENT TOTALLine 3577: cmd.CommandText = "SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID=@LoanID";Line 3578: decimal split_currentamt = ((decimal)cmd.ExecuteScalar()); 

View 6 Replies View Related

Convert Varchar To Date In Where Condition

Mar 31, 2008

Hi guys.
i have a field in a table which datatype is varchar and i m storing datetime and number in it.. but when i try to use select statement like ( between startdate and enddate) or number (= ) operator it's not working..

select * from table
where cast(Field as datetime) between @startdate and @enddate



second query that i m trying to exectue is

select * from table
where cast(Field as bigint) = @Fieldid


but both are not returning any value...can anyone tells me where i m doing mistake

View 21 Replies View Related

Convert Char To Decimal

Sep 29, 2007

 Hi all,I have a column LateHours (Varchar).  I want to put it in another table which has Latehours column in Decimal(4,2) Example   =  +04:33    Should be 4.33   (Only + values)                      Char   to               Decimal (4,2)   Please Help me,Thanks,Janaka 

View 3 Replies View Related

Convert Binary To Decimal In A SP

Jun 3, 2002

Hi,

I need to write Stored Procedures to convert a Binary number to a Decimal number and Decimal back to Binary (i.e. 2 sp's).

I don't have a clue how I'm to do this. Can anyone help me !?

Pieter

View 1 Replies View Related

How Can I Convert Decimal To Hexadecimal

Mar 14, 2006

Hi!!!!!


I'm looking for a SQL FUnction that convert a decimal to Hexadecimal and

Hexadecimal to decimal data.

I know the way to convert for. But not with a SQL Function. certainly I

need to know How to express an Exponential Function.


Thank's.

View 2 Replies View Related

How To Convert Number To 2 Decimal

May 23, 2012

i have 001747254 and 000096710 in column 'price'i want it to display as 17472.54 and 967.10how can i do this in one query?

View 4 Replies View Related

SQL 2012 :: Won't Convert To Decimal

Feb 3, 2015

I'm trying to insert into a table from an XML file. The mapping works OK however there is a problem with one of the fields. It is field name "Length" set up as Decimal(18,2) and it stops on the first row with an error, something like "Cannot convert to decimal". The values are all integers, such as "9", etc. but I presumed SQL would convert to "9.00" for example. It has worked for another field name "Weight", where values are stored in the XML file such as "0.28", etc. Does it reject it because it's an integer and needs to be to two decimal format in the XML?

View 9 Replies View Related

How To Convert From Decimal To Binary In SQL?

Apr 10, 2008

Does anybody know
how to convert in SQL a number from decimal to binary:
Example: 'F' = 1111

I tried select convert(binary, 12.22) but SQL interprets the word 'binary' as Hex.

Thanks a lot!

View 10 Replies View Related

Convert Value To 2 Decimal Places?

Sep 6, 2007


Hello, is there a way to convert the value to just 2 decimal places, I created the report in Reporting Services and it has quite a few digits to each value. I looked at the table and found that the data type is {Float}. Is there a way to convert the values to just 2 decimal places?..Thank You.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved