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
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 RelatedHi,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 RelatedI 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
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)?
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
Can anyone explain in simple terms the difference between cast and convert in SQL?
View 2 Replies View RelatedHi 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
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.
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?
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.
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 RelatedI 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
1)Cast(isnull(Partl,0) as smallint)
2) convert(varchar(12),newDate, 101)
what two lines would do. ??
thanks,
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
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';
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 RelatedI 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
Hi,
I have a table with the following structure:
Table: Meeting
ID integer not null
Desc text null
Here Col 'Desc' is a text type stores description. I have used the following UPDATE query:
UPDATE Meeting SET Desc='New Updated text......' + ' ' + CAST(Desc as varchar) WHERE ID=100
Issues:
1. Since i did'nt specify lenth for varchar it takes only 30 chars from existing value and added to the edited text.
2. If i give like this CAST(Desc as varchar(8000)); it might take only first 8000 chars; remaining would be truncated correct? I assume that will happen.
3. If i don't use CAST i get error msg: "Invalid operator for type.."
Is there any work around in this situation? or better way to do it.
Looking for your help.
Thanks
BoB
I'm trying to cast the variable Alumni and keep getting a "The type of the value being assigned to variable "User::
Alumni" differs from the current variable type" error. The resultset from Records is a varchar(max) and the variable Alumni is a string. I found a blog that says to cast the result column to a max size limit. I want to do it this way so I can put the result in the message of a send mail task.
Here is the blog.
http://blogs.conchango.com/stevewright/archive/2006/01/20/2686.aspx
SELECT Records as Alumni
this doesn't work or I dont' have the syntax right?
SELECT Records as CAST(Alumni as varchar(max))
thanks,
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
I'm trying to do something like this SQL statement. I have a table with a field of date/times.
i.e - 2/27/06 9:55:95 PM Basically there are multiple entries in the table per day.
I want to count the records for particular (hence the CAST) and output the count.
Here it was I had just to see if SQL would work in ASP.NET :
SELECT COUNT(*) AS Expr1FROM dbo.tblActiveDrumsWHERE [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0' OR NCTimeStamp - [CAST](FLOOR([CAST](NCTimeStamp AS [float])) AS datetime) = '0'
It didn't like the AS references?
Any help would be appreciated.
Hi
Can someone help me in the following:
An example is shown below, I had assign a date (format: dd/mm/yyyy) to @vdate. Follow by a cast to vchar. The issue is that the date format is changed after the cast. I would like the date format to remain as dd/mm/yyyy after doing the cast. Hope someone can give some advices. Thanks alot.
Example:
declare @vdate as datetime
set @vdate = convert(datetime,'01/10/2005',103)
select @vdate --> '10/01/2005' (date format required)
cast(varchar(20),@vdate) --> 'Oct 1, 2005' (date format changed)
When I test the CAST function on AdventureWorks2012, the CAST function will round down or up, i.e. 2024.994 to 2025 or 5.1865 to 5.
However, when I try the same the CAST function as:
SELECTCAST(2024.994 AS INT) the result I get is now 2024 (and not 2025).
Why is CAST rounding up when I use it on a table in the AdventureWorks2012 database, but when I use CAST on its own, it rounds down?
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
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?
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedUsing 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."
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
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.
I am trying to concatenate a string to a currency amount so that I can show both in a drop down list box. However when I create the SQL to do this using the Query tool I get a message to say that I must use the Convert funtion to convert the currency amount.Anyone know the SQL syntax for this function as I can seem to get it to work. ThanksPaul
View 3 Replies View RelatedI'll go right to the point:
I have a textbox in wich the user can seize a "money" value.
But when i'm doing the INSERT command, it fails and says I cannot implicitely convert nvarchar data (mytextbox.text) to money data.
I don't know how to handle this...
objCmd = New SqlCommand("INSERT INTO tbl_appel_service_pieces " & _
"(fld_nom_piece, fld_quantite, fld_prix, fld_description, fld_num_appel) " & _
"VALUES (@fld_nom_piece, @fld_quantite, @fld_prix, @fld_description, @fld_num_appel)", objConn)
objCmd.Parameters.Add("@fld_nom_piece", champsNomPiece.Text)
objCmd.Parameters.Add("@fld_quantite", champsQuantite.Text)
objCmd.Parameters.Add("@fld_prix", champsPrix.Text)
objCmd.Parameters.Add("@fld_description", champsDescription.Text)
objCmd.Parameters.Add("@fld_num_appel", champsNumAppel.Text)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
afficherPieces()
How and where to do the conversion ? On the VALUES variable ?
Hi, I have found some ways on using Convert() function for SQL server. But i do not understand some of them.
Example:
CONVERT(VARCHAR(10),column name,108)
What is the 108 for? I see some with 120 and 101. I tried all but only 108 is working for me the way I want..but i do not understand what is that for actually. Can anyone explain it to me? Thank you.