Copy current record

Hi There
Tablename "tblAppDetails"
Which includes the following fields
AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
varchar 100, AppAddress1 varchar 100
What I'd like are 2 SP's, the first to select the all the fields (other than
AppDate & AppTime) for the current record and the second to paste those
values onto another record in the same table (this is for moving someone fro
m
one appointment to another. The user sees one record to view and'll click a
button to copy, select a new record and click another button to paste) .
Is this something to do with Fetch?
Many thanks
PaulPaul in Harrow wrote:
> Hi There
> Tablename "tblAppDetails"
> Which includes the following fields
> AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
> varchar 100, AppAddress1 varchar 100
> What I'd like are 2 SP's, the first to select the all the fields
> (other than AppDate & AppTime) for the current record and the second
> to paste those values onto another record in the same table (this is
> for moving someone from one appointment to another. The user sees one
> record to view and'll click a button to copy, select a new record and
> click another button to paste) .
> Is this something to do with Fetch?
> Many thanks
> Paul
The PK in the table seems a little iffy. What prevents two records from
having the same date and time?
With that PK, why not just change the date and time to make the change.
I assume by "current record" you mean a row of data that a user is
editing. I'm not sure what you mean by "another record" because of the
PK.
Could you explain in a little more detail what data you want to move,
where it's going and if any other tables are involved in the process.
David Gugick
Imceda Software
www.imceda.com|||You don't need cursors and multiple SPs. A single UPDATE will do it:
UPDATE tblAppDetails
SET appname =
(SELECT appname
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1),
(SELECT appaddress1
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1)
WHERE appdate = @.ad2
AND apptime = @.at2
(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively.
Why are you using separate columns for date and time when SMALLDATETIME
stores both?
David Portas
SQL Server MVP
--|||Thanks both Davids
"David Portas" wrote:
"Why are you using separate columns for date and time when SMALLDATETIME
stores both?"
I know, but this is not my table and it's been pre-filled with 24 AppTimes
for each AppDate for the next five years!
"(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively."
Er, how? @.ad1 &@.at1 are from the current record but how does it know which
is the second record?|||In my query you need to specify the keys for both the source row and
the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
"second row"? You didn't specify how the target row for the change is
to be determined so I assumed you were supplying that information as
parameters in your SP. If you wanted something else then please give us
a full description of the problem: DDL, sample data INSERT statements
and show your required end result. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||David,
I'll do all this on Monday
Paul
"David Portas" wrote:

> In my query you need to specify the keys for both the source row and
> the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
> "second row"? You didn't specify how the target row for the change is
> to be determined so I assumed you were supplying that information as
> parameters in your SP. If you wanted something else then please give us
> a full description of the problem: DDL, sample data INSERT statements
> and show your required end result. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>

Copy a column content in another one only if empty

for MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you--I ve consider null value also in this code.
update Users
set Alias=Name where Alias is null or ltrim(rtrim(Alias))=''|||wonderfull !

it works :-)

thanks a lot

Convertion VarChar Error

Ive got a small problem at the moment

I have ran a query that has been used for a while now and have recieved this error

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'N' to a column of data type int.

Ive searched the data and the only value of N that i can find is currently sitting in a field where the field type is varchar

is there a workaround for this, Ive tried running a case statement to set the N to 0 and also tried casting

Cheers in advance
Dave...the only value of N that i can find is currently sitting in a field where the field type is varcharso why is it trying to convert this value to an integer?

i have no idea, because i can't see your query from here

:)|||rudy man i thought you were psychic

-- 5302 MH consultant OP first attendances

SELECT CdsType,
NHSTrust,
AttendedOrDNACode, --N apears here varchar(1) column
FirstAttendanceCode,
SpecialtyCode,
Specialty,
PCG,
PurchCode,
datepart (year, ActivityDate) as yearAct,
datepart (month, ActivityDate) as monthAct

FROM dbo.VIEW_Outpatient2000_Analysis

WHERE PurchCode like '5KW%'
--pcg like 'Chelt%'
and NHSTrust not like 'Glou%'
and FirstAttendanceCode = 1
and AttendedOrDNACode in ('5', '6', '1','N')
and SpecialtyCode between '710' and '715'
and ((datepart (year, ActivityDate) = 2004
and datepart (month, ActivityDate) > 03)
or (datepart (year, ActivityDate) = 2005
and datepart (month, ActivityDate) < 04))

So im pretty much confused, cant see anything there that would cause a problem|||the 'N' may not necessarily be where you think it is

FirstAttendanceCode = 1|||Ah Blind as a Bat i am
Cheers rudy

Converting/Casting strings into Datetime datatype

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?

Thanks!

If you had only stored your date values in the ISO format of YYYYMMDD, they would easily cast or convert to datetime. -Or even left in one of the standard date delimiters, such as [ / - ].

However, you (or some unnamed 'other' person) made up a oddball format, and now you will have to 'handle' it to create a 'real' date value everytime you need to use it.

(This assumes your format is MMDDYYYY.)

SELECT cast( stuff( stuff( '03032007', 3, 0, '/' ), 6, 0, '/' ) AS datetime )

-
2007-03-03 00:00:00.000

|||

You know that MS stores sqlagent datetime in to two int columns with the following format, right? (Take a look at the schema for msdb: sysalerts,sysjobhistory, sysjobschedules, sysjobservers, and sysjobsteps.)

date: YYYYMMDD

time: HHMMSS

So, it's not that weird to see the public employs such schema.

|||

But I also notice that MS stores SQL Agent datetime in ISO format (YYYYMMDD).

That little 'standard' makes a lot of difference in cast/convert.

That is behind my even mentioning using a standard ISO format in my response...

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
Why are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegrou ps.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
--
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert
(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), conve
rt(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting varchar to int (or numeric)

In one of my stored procedures, a varchar is input that is assumed to be an int, and I need to validate that it is an int before I CAST it as an int. I'm currently using ISNUMERIC() to eliminate non-numeric values, but how do I recover gracefully if values such as '7.5e3', '$334', or '45.9943' sneak through? They all pass the ISNUMERIC() test, but cause errors when being cast to int. Similar problems if casting to numeric, etc.You can use the case statement - for example:

declare @.test varchar(20)
select @.test='123.3'
select case when charindex('.',@.test) > 0 then cast(@.test as decimal(10,2))
when charindex('e',@.test) > 0 then cast...
else cast(@.test as int) end|||Thanks. That should work. On retrospect, it's obvious enough I should have come up with that myself!

converting varchar to int

Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...

how to overcome this problem ..

Hello Raj,

What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439? Try this:

=cInt(Replace(Fields!Field1.Value, ".", ""))

Jarret

|||when i run the query it throws the error... i have to handle the error in the data tab itself... can you help me how to find the exact field in which the irregular datatype is located ...is there any way to track down the issue...|||

It is clearly an SQL error which cannot be solved in SSRS using expressions.

I'm assuming that your dataset is formed by a stored procedure, not plain SQL text.

The error is because you are trying to put an invalid integer value into a table (temporary table or user table) column which is of INT datatype. If possible, get the SQL text of the stored procedure and check all insert/update statements and all the columns of INT datatype involved in it. Also check If there is an implicit conversion using CONVERT or CAST function.

Shyam

|||can you tell me the problem is with varchar or datetime... because i didnt find any varchar like '3.1.7.4.3.9.'|||

Yes, it is definitely a varchar. You may have to look out for columns in the table that is being used as source to insert/update the value in the table which has a corresponding column of INT datatype.

Shyam

|||thank you shyam .. i will check it out...

Converting varchar to int

Hi,
I have a varchar(255) column where I may have data like this:
43294430949
adkk3400
1056
ff1d
10
302
15000043
I would like to write a SQL query that returns values between
10 and 500 (numeric)
If I just do this:
Select * from table where column between '10' and '500'
I would also get 15000043. That's incorrect
I also tried doing this:
Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
column) <=500
but it fails when I have characters in the column.
Do you guys know how I can do that?
ThanksFirst you need a solid function that can determine if the value is numeric.
For
that go here: http://www.aspfaq.com/show.asp?id=2390.
Select *
From #Test As T
Where IsNumeric(T.Data) = 1
And dbo.IsReallyInteger(T.Data) = 1
And Cast(T.Data As BigInt) Between 10 And 50
Why the two checks? If you only use IsReallyNumeric, SQL cannot determine wh
at
that function actually does and more specifically, whether it filters for va
lues
that will be castable to BigInt. Then why use IsReallyInteger in the first
place? The reason is that IsNumeric is faulty in its determination of numeri
c
values. Characters like "$" and "d' and other odd characters can return true
for
a IsNumeric.
HTH
Thomas
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int, colum
n)
> <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks|||Try,
Select *
from table
where
case
when c1 like '[0-9][0-9]' or c1 like '[0-9][0-9][0-9]' then cast(c1 as int)
else null
end between 10 and 500
AMB
"Star" wrote:

> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks
>|||You can write your query with a WHERE clause like:
WHERE CASE WHEN ISNUMERIC( col ) = 1
THEN CAST( col AS BIGINT )
END BETWEEN 10 AND 500 ;
Note that there are certain considerations with ISNUMERIC with characters
like e, d, $ etc. in which case you'd have to use PATINDEX to make sure the
values are numerically compatible. Also, is the converted value is beyond
the value limitations of INT or BIGINT or even DECIMAL values, then you'll
get an overflow error.
Anith|||We need more information:
1) How should the values such as 'adkk3400' be considered? Do you want this
to be 3400 numeric, or do you want to ignore rows with nun-numeric content?
2) For the numeric values, the 'int' data type would not work for your first
value '43294430949' - it is outside the rane of acceptable values. Would
'bigint' be OK?
So, for example, if you are ignoring rows with alphabetical characters, and
your data only contained numbers and letters, you could try something like
this (untested pseudo-code, since you did not provide DDL, sample data, or
expected results [http://www.aspfaq.com/etiquette.asp?id=5006]):
SELECT <Final Column List>
(SELECT UglyDataColumn, <Other Column List>
FROM MakeBelieveTable
WHERE UglyDataColumn NOT LIKE '%[A-Za-z]%') NUMONLY
WHERE CAST(NUMONLY.UglyDataColumn AS bigint) BETWEEN 10 AND 500
If this is not what you are looking for, you will need to provide better
specifications.
P.S. Out of curiosity, what type of information exactly is this
'UglyDataColumn' holding? That is a seriously bad assortment of values, and
I am guessing there are either some missing constraints on that column, or
the design is fundamentally flawed.
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks

Converting varchar to decimal

I'm have a varchar(50) field that I want to convert to decimal. These are
two samples:
+000000063451473.38
-000000038818201.42
Logically I want to:
-remove the + sign and leave the - sign
-remove any leading 0s
My desired outcome is:
63451473.38
-38818201.42
How can I reliably do this. Thanks to anyone who could help.Actually this should be down in the frontend after dataretrieval, because
string functions are not that really fast in SQL Server (2000).
DECLARE @.Number2Convert Varchar(50)
SET @.Number2Convert = '-000000063451473.38'
SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||something like this should do:
select str(your_col,18,2)
from tb
-oj
"Terri" <terri@.cybernets.com> wrote in message
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||Thanks Jens, I need to calculate with this data before it even will reach
the front end and it will be a once a day process with minimal records so
performance is not critical here.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eN3i9fAWFHA.2928@.TK2MSFTNGP10.phx.gbl...
> Actually this should be down in the frontend after dataretrieval, because
> string functions are not that really fast in SQL Server (2000).
> DECLARE @.Number2Convert Varchar(50)
> SET @.Number2Convert = '-000000063451473.38'
> SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
>
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
> news:d6389k$9ht$1@.reader2.nmix.net...
are
>|||Terri
Use Cast Function.. Example
Select Cast('+000000063451473.38' as Decimal(38,3))
Charly
"Terri" wrote:

> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>

Converting varchar to DateTime

Hi,

I wanted to convert the varchar to date time and here is what i am doing

DECLARE @.dt VARCHAR(20)

SET @.dt = '20070111' -- YYYYMMDD format

select CONVERT(datetime, @.dt, 120)

This works perfectly fine and the result would be- 2007-01-11 00:00:00.000

But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

"Conversion failed when converting datetime from character string."

Can any one please let me know how do we achieve this?

~Mohan

YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

|||

This is the Convert sintax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:mis.

Then, ?Does because a string with the format yyyymmdd can be converted to string?:

The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

I recommend to you, to pass strings in the yyyy-mm-dd hh:mis format to be recognized by the CONVERT or CAST functions in SQL server.

Converting varchar to datetime

I have a sql server 2000 db that has a carchar field that is currently storying date data in the following format:

June 16

Can I convert that from varchar to datetime or smalldatetime without loss of data? And, if so, what adjustments do I have to make in my ado code to continue to allow my clients to add data. Currently they add date data by selecting a month from one dropdown and the day from another.

Thanks!Create another column and use CONVERT function to conver the date and store. Then delete the old column.

converting varchar to date from asp.net to sort

for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:

CREATE PROCEDURE GetAllWeekEnding

AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO

if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.

Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand

MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()

Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While

mydr.Close()
MyConnection.Close()

the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion

any ideas?the .Add method expects a ListItem or a String. Try:

DropDownList1.Items.Add(mydr("we2").ToString())

Is there any reason you are not just binding to myDr?|||<<<<Is there any reason you are not just binding to myDr? >>>
yes, when i bind to the listbox, i am getting date and time.

i can get rid of the time by using:

While mydr.Read()
DropDownList1.Items.Add(Format(mydr("we2"), "M/dd/yyyy").ToString)
End While|||You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.|||<<<<<You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.
>>>>
the problem is that the date is already in varchar datatype in the database. on the aspx page, i want to load all the dates and i want it sorted by date. the only way for me to show the dates on the aspx page sorted by date is to convert the date from varchar to datetime, like this:

CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct convert(datetime, we) as we2 FROM tblArchive order by we2 desc
GO

once varchar is converted to datetime, it not only shows just the date but also time so i cannot bind it to a dropDownList.

if i just bind the orginal varchar on the DropDownList witout converting it to DateTime, it's not going to be sorted because you cannot sort a varchar like you sort a datetime|||CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct CONVERT(nvarchar(20),convert(datetime, we),101) as we2 FROM tblArchive order by convert(datetime, we) desc
GO

There is no rule that the representation you SELECT needs to be the same as you use to ORDER BY.

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri :)
Uri Dimant wrote:
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> > Hi
> > Not being a coder I have no idea how to do this and existing posts dont
> > really help.
> >
> > I have a table called 'Incoming' and a column called dateofbirth. The
> > format of the column is varchar. The values in the dateof birth column
> > are 01012000. When searching for particular dates I'm getting crap
> > results (because the query is crap too). I reckon I need to convert the
> > values in the column to dates so that my query can work.
> > All help gratefully accepted.
> > TIA
> >|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),11
2)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri
Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),
112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting varbinary to varchar

I have a password field which is of varbinary. Since its a varbinary Icannot see the password in the database I only see hexadecimal values.Now my question is that how can I convert those hexadecimal values tostring or varchar so I can read the password.
any ideas ??

Not sure if you seen this or not, but it talks about ways to convert binary fields.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Nick

Converting Timestamp to varchar or concatenating it with a string

Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT'

DECLARE @.Result INT

EXEC @.Result = DELETE_DOCUMENT

@.DocumentID = ' + STR(DocumentID) + ',

@.TimeStamp =' + CAST([Timestamp] as varchar) + ',

-- CHECK RESULT AND STATUS

-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL

FROMDocument

The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

T

T?

T-

xnT

T!

T"

T#

T$

T%

T&

T'

T(

T)

T*

T+

T,

instead of

0x0000000013540F1C

0x0000000013540F1E

0x0000000013540F1F

0x0000000013786EDE

0x0000000013540F21

0x0000000013540F22

0x0000000013540F23

0x0000000013540F24

0x0000000013540F25

0x0000000013540F26

0x0000000013540F27

0x0000000013540F28

0x0000000013540F29

0x0000000013540F2A

0x0000000013540F2B

0x0000000013540F2C

which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks

Sam

use tempdb

go

create table dbo.t1 (

c1 timestamp

)

go

insert into dbo.t1 default values

go

select

c1,

master.sys.fn_varbintohexstr(cast(c1 as varbinary(8)))

from

dbo.t1

go

drop table dbo.t1

go

AMB

|||

hi

Thanks for that I have been looking for that kind of function every where

Regards

Sam

converting the varchar value to int

All,
I am running this statement and getting the error below. I am assuming
that the issue is the CLCCHRGE.CCOUNT data type is Varchar and needs to
be converted using a CONVERT clause or a CASE statement but I am not
sure I am on the right path here and if I am then I am not sure of the
syntax.
SELECT CLCHRGE.CCOUNT, CLCHRGE.CPTPRT, CLCHRGE.XCDATE,
CLMSTER.PLNAME, CLMSTER.PFNAME, procdesc, dignosis
FROM CLCHRGE INNER JOIN
CLMATER ON CLCHRGE.CCOUNT = CLMSTER.CCOUNT
WHERE (CLCHRGE.XACDATE BETWEEN '2005-01-01' AND '2005-1-31')
AND (CLCHRGE.CPTPINT = '90801') AND CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
order by xacdate, plname desc
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'C171913' to a column of data
type int.Try changing this
CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
to this
CLCHRGE.CCOUNT in ('608685', '477078',
'608201', '204739')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Success!, Thank you for the tip!! You are a Lifesaver!