SQL Server 2012 :: Using CAST Or Convert Functions

May 31, 2015

How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).

Calculation: @Diff = (@EndTime - @StartTime)

I still want the variables (@StartTime and @EndTime) to remain as nvarchar.

The code:

DECLARE @StartTime nvarchar(10) = '12:10';
DECLARE @EndTime nvarchar(10) = '12:30';
DECLARE @Diff time(1) = '00:00';
SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';

View 7 Replies


ADVERTISEMENT

Error When Convert Or Cast Functions From Varchar To XML Datatype

Dec 29, 2007

Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems.
When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below 
I am trying to do convert(xml,MyVarcharColumn)
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Also i tried with casting and getting same problme. is there any way to convert
 
please suggest me
 
Thanks
Dilip

View 1 Replies View Related

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.

Aug 15, 2006

Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

View 4 Replies View Related

SQL Server 2012 :: Odd Cast Of Exponential To Float?

Mar 23, 2015

For whatever reason i'm unable to cast anything more thtan e-4 to a float which makes no sence. Am i missing something?

select cast( '1.550e-6' as float)
?????????
returns 1.55E-06
????????
select cast( '1.550e-5' as float)
?????????
returns 1.55E-05
????????
select cast( '1.550e-4' as float)
returns 0.000155
select cast( '1.550e10' as float)
returns 15500000000

View 9 Replies View Related

Cast Or Convert

Feb 18, 2008

 Hi,I want to turn int to double/decimal  in microsoft sqlSHould i use cast or convert?if so, how i do it thanks, 

View 1 Replies View Related

CAST/Convert

Mar 15, 2006

I need Query syntax to cast/convert values as follws.

Val.: 00005000010260002180 - Result must be: 5.1.2600.2180
Val.: 00005000000213400001 - Reslut must be : 5.0.2134.1

Dots must also be contained in result

View 2 Replies View Related

CAST Or Convert

Oct 8, 2014

Have the following in my SELECT statement, which I'm having issues with when I modify it to include a CAST or a CONVERT.

CONCAT(PER.[PERSON-REF],ROW_NUMBER() OVER (PARTITION BY PER.[PERSON-REF] ORDER BY TEN.[tenancy-ref])) AS 'ID'How do I convert or cast it to a varchar (20)?

View 8 Replies View Related

Convert AND Cast

Nov 6, 2007

We have a 3rd party application that connects to our SQL 2005 as a external database source.

Our external database has a 'datein' field that for some reason is stored as a varchar(50).

I can use cast to convert the field to datetime, but how would I then format to the yyyy-mm-dd format in the same select statement(using in view)?

I need to convert this field to a format of YYYY-MM-DD in order for the 3rd party application to search on it correctly.
Below is an example of the data --
FieldName = DateIn
DateType = Varchar(50)
2006-07-06
Sep 12 2007 11:16AM
2007-07-19
Oct 2 2007 12:24PM
2007-08-19
Oct 31 2007 8:00PM
Sep 15 2007 9:25AM
2007-08-18
Oct 16 2007 7:01PM
Oct 30 2007 11:15AM
Aug 29 2007 8:54AM
Aug 29 2007 8:38PM
Aug 25 2007 2:33PM

View 3 Replies View Related

Difference Between Cast And Convert In SQL

Apr 20, 2007

Can anyone explain in simple terms the difference between cast and convert in SQL?

View 2 Replies View Related

Cast/Convert Value To VB Variable

Oct 24, 2005

Hi all,
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.

Thanks in advance for all your help.

Regards:
Prathmesh

View 2 Replies View Related

Update With Different CAST And CONVERT

Jul 6, 2014

I have a table with all varchar data for all different fields for money and int etc. I want to do a calcuation in one field with the rule as below:

UPDATE [dbo].[tblPayments]
SET [PreInjuryWage]=
CASE
WHEN [WeekNo]<14
THEN ([MaxWorkCover]+ [WeeklyEarnings] )/0.95
ELSE ([MaxWorkCover] + 0.80 * [WeeklyEarnings] ) /0.80
END

I tried this command after many other ways of tries

UPDATE [dbo].[tblPayments1]
SET [PreInjuryWage]=
CASE
WHEN [WeekNo]<14
THEN CAST ( (cast([MaxWorkCover] as money )+ cast([WeeklyEarnings] as money ))/0.95 AS Varchar(10) )
ELSE CAST ( (convert(money, [MaxWorkCover] + (0.80 * convert(money, [WeeklyEarnings] )) )/0.80 ) as varchar(10))
END

The structure of the table ( its a legacy table from 2000 i beleive)

CREATE TABLE [dbo].[tblPayments](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[ClaimID] [int] NOT NULL,
[WeekNo] [smallint] NULL,

[code]....

Arithmetic overflow error converting numeric to data type varchar.

View 6 Replies View Related

The Difference Between Cast And Convert

May 14, 2008

Can anyone tell me what is the difference between Cast and Convert...?

BOL tells me that they provide similar functionality; it doesn't say that they provide the same functionality.


What is the purpose of having both... is the reason historical?

Which one performs better?

View 3 Replies View Related

Cast Or Convert -2 To Just Show 2

Mar 24, 2008

ToMatch=58
ToNoMatch=1


select @Agg1= Cast(cast(cast(cast(Max(TotMatch) as decimal) / cast((Max(TotNoMatch) + Max(TotMatch)) as decimal)* 100 as int) - 100 as int) as varchar) + '%' FROM #Rpt;

if i try this it is giving me answer in -2%
how i can remove the - sign



also if I try the same statement in different way. well I am going to post in other post.

View 3 Replies View Related

What Is Diff Betn Cast And Convert

May 22, 2008

 Hi alll,can anyone tell me that In which cases cast is used in db and what is diff betn cast and convert

View 9 Replies View Related

Cast Or Convert And Aggreate Function

Dec 1, 2000

Hi people,
I am trying to use Aggregate function like count or average on a column which has a datatype Varchar. In order to use avg or count on it , I am doing a cast on it.
If somebody has use Cast/Convert with aggregate function please help me
Thanks
Jesal

View 2 Replies View Related

CAST Or CONVERT When Calculating AVG From INTs?

Nov 13, 2013

I have an INT field that I want to take the average value of, to the 2nd decimal. Is there a difference between using CAST vs CONVERT?

CAST

Code:
CAST(Avg(CAST(units AS DECIMAL(10,2))) AS DECIMAL(10,2))
from myTable
CONVERT

Code:
CONVERT(numeric(10,2),avg(CONVERT(numeric(10,2),units)))
from myTable

View 4 Replies View Related

Cast Convert(varchar(12),newDate, 101)

Mar 25, 2008

1)Cast(isnull(Partl,0) as smallint)
2) convert(varchar(12),newDate, 101)

what two lines would do. ??

thanks,

View 1 Replies View Related

Convert Or Cast HexaDecimal To Bigint

Aug 21, 2006

Hi ,

I have a hexadecimal string value. I want to convert it to Bigint in Sql Server 2005.

The Hexadecimal value is '0x000000000000000F'.

How is it possible to convert into Bigint.

Please help me

Thanks in advance

Srinivas





View 6 Replies View Related

Order By Case Cast Convert Error

Jul 23, 2005

I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End

View 11 Replies View Related

Date Conversion Woes: Cast? Convert?

Nov 7, 2007

I have been having some trouble trying to get the date format YYYYMMDD 00:00:00.00 to convert to MM/DD/YYYY. I am using SQL 2005 and Reporting Services. I attempted several variations to solve this problem as outlined below, with the final working conclusion at the end. I am not saying that this is the BEST or the ONLY way to achieve the solution, but it worked for me.



Code Block
---------------------------------------------------------------------------------------
SELECT DATE_TIME AS DATE1
FROM TRANSLOG
--returns value of 20071027 02:26:24.06
---------------------------------------------------------------------------------------
SELECT CAST(DATE_TIME AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 02:26:00
---------------------------------------------------------------------------------------
SELECT LEFT(CAST(DATE_TIME AS SMALLDATETIME),11) AS DATE1
FROM TRANSLOG
--returns value of Oct 27 2007
---------------------------------------------------------------------------------------
SELECT CAST(SUBSTRING(DATE_TIME,0,9) AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 00:00:00
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), DATE_TIME, 103)
FROM TRANSLOG
--although this works with GETDATE(), it does not work with the DATE_TIME field
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), CAST(DATE_TIME AS SMALLDATETIME), 101) AS DATE1
FROM TRANSLOG
--THIS WORKS!!! Displaying 10/27/2007





Hope this helps somebody someday!

-Jody

View 5 Replies View Related

Problem With CAST And CONVERT In SQL Server2000 Converting Decimal Places From 4 To 2

May 11, 2005

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted. 
For example: 
Database Price            SELECT statement Price
100.56                           100.5600
I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56.  Has anyone had a similar problem?
Thanks

View 5 Replies View Related

T-SQL (SS2K8) :: Using CAST Or CONVERT To Change Data From String To Datetime?

Apr 16, 2014

i am trying to take a field that has part of a date in it, so I have to parse it out as follows:

SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)

This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.

From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.

When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."

CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)

I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.

how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?

View 9 Replies View Related

SQL Server 2012 :: User Defined Functions - Include Comments In Parameter Intellisense?

Aug 22, 2014

If you use the LEFT() function for example it provides intellisense support for some of the required parameters.

Is there a way to get that same descriptive text in user defined functions?

Some comment block you define when creating the function?

View 4 Replies View Related

CS0266: Cannot Implicitly Convert Type 'object' To 'bool'. An Explicit Conversion Exists (are You Missing A Cast?)

Apr 20, 2008

Using OnSelected on the SqlDataSource----here is my currect code:private void On_Load(Object sender, SqlDataSourceCommandEventArgs e){if (e.Command.Parameters["@DueDate"].Value = "NULL"){e.Command.Parameters["@DueDate"].Value = "0000-00-00 00:00:00.000";}}
I am trying to change the value from  NULL(in a datetime field) to "0000-00-00 00:00:00.000" for use on the page.Any ideas, suggestions, or thoughts would be greatly appreciated!
,Gary"we truly fail, only when we quit."

View 2 Replies View Related

SQL Server 2012 :: How To Convert Varchar Value To INT

Apr 25, 2014

I am new to T-SQL development and here's what I am trying to do.

declare @cmd varchar(255)
set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'

while (@cmd <=13)
begin
insert into #dbcheck values (@cmd)
Set @cmd = @cmd + 1
end

but it is giving me an error

'Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'

Note - #dbcheck has only one column id int

View 3 Replies View Related

SQL 2012 :: Unable To Cast COM Object

Dec 31, 2014

I have already registered the Dts.dll, but it did not make any change, also I have update the visual studio to latest update 4.I have registered the dll by using CMD to the below location and entering regsvr32 dts.dll, it brought up a popup message saying the dll the has been registered.

C:Program FilesMicrosoft SQL Server100DTSBinn

But the issue remains the same, I have to click ok on the popup msg for about 20-30 times to make it go away and the dtsx package in the solution work just fine after closing the pop up.

unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{203E24BD-5203-4C76-AFC7-7C8545EBA1A0}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)). (Microsoft Visual Studio)

View 4 Replies View Related

SQL Server 2012 :: Convert Parts Of String To Int

May 2, 2014

I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".

I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.

CREATE TABLE #tmp(duration VARCHAR(20))

INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec

And this is what I have so far

SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0

[Code] ...

I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.

View 3 Replies View Related

SQL Server 2012 :: Convert Integer To Date

Oct 16, 2014

In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?

In short, how can I convert a 100 year date to Gregorian (any format)?

View 3 Replies View Related

SQL Server 2012 :: Convert Integer To Time?

Dec 3, 2014

I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.

0 = 00:00
30 = 00:30
...

1700 = 1700 etc...

How can I convert this to a real time field?

View 9 Replies View Related

SQL Server 2012 :: Convert Varchar To Time

Mar 9, 2015

I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.

CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time

select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')

View 2 Replies View Related

SQL 2012 :: Cast Varchar To Bigint Error

Mar 4, 2014

I try to run code but got an error. How to fix it?

SELECT Cast(('20140304800084500001') AS BIGINT)

Arithmetic overflow error converting expression to data type bigint

View 1 Replies View Related

SQL Server 2012 :: Convert Bigint Time To Datetime

Apr 17, 2014

How to convert bigint time to datetime (CST)

Declare @MyBigIntTime BIGINT =1397750400000

View 9 Replies View Related

SQL Server 2012 :: Convert Function Inconsistent With XML Entities?

Aug 27, 2014

I have the following data stored in a column in a table:

£10,000.00 &amp; &apos; &quot; % &lt; &gt; Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones

I query this in a stored procedure using the FOR XML clause and universal table, and store the result in an XML data type variable:

DECLARE@ResultXML
EXECUTE[someStoredProcedure] @Result OUTPUT

This data (as XML) is then written to a file; in order to do this, I CONVERT the data to NVARCHAR since there are unicode characters in the source:

DECLARE@strResultNVARCHAR(MAX)
SET@strResult= CONVERT( NVARCHAR( MAX ), @Result, 1 )

Now this works fine, except on inspection, SQLServer has decided to render the data thus:

£10,000.00 &amp; ' " % &lt; &gt; Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones

Why it has changed the apos and quot entities to the corresponding character but not the other entities is beyond me.

how to preserve XML entities?

View 0 Replies View Related







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