How to remove time from date?

Hello,
I have a table (T1) that has a field that holds a date with a time. In
another table (T2) I have records related to T1 on an ID field (one T1 to
many T2). T2 has a date field also, but this field does not have time (ie
the time part is midnight). I want to join the two tables on the ID and
Date but the time portions of the dates are not equal so they do not match.
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND T1.Date = T2.Date
Does anyone know how I can set the time portion of T1.Date to midnight in
the query (not in the table) so the join will work.
I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
want the time part dropped or midnight.
Thanks
EdmundHi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry|||I don't know why I did not think of this. It was staring right at me.
Thanks Barry. I've gone for CONVERT(CHAR(10),T1.Date, 120)) as it will
implicitly convert to DATETIME with date as midnight.
Edmund.
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1149517628.941905.154220@.h76g2000cwa.googlegroups.com...
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry
>|||try this:
CONVERT(VARCHAR(10), T1.date, 126)
Barry wrote:
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry|||Edmund,
You might want to chk this article :: http://www.aspfaq.com/show.asp?id=2460
Extract from that article:
--
For example, to get today's date in YYYYMMDD format, you currently need to
call the following:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
What does the 112 mean? Nothing. It's just an arbitrary number representing
this specific format.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Edmund" wrote:

> Hello,
> I have a table (T1) that has a field that holds a date with a time. In
> another table (T2) I have records related to T1 on an ID field (one T1 to
> many T2). T2 has a date field also, but this field does not have time (ie
> the time part is midnight). I want to join the two tables on the ID and
> Date but the time portions of the dates are not equal so they do not match
.
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND T1.Date = T2.Date
> Does anyone know how I can set the time portion of T1.Date to midnight in
> the query (not in the table) so the join will work.
> I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
> want the time part dropped or midnight.
> Thanks
> Edmund
>
>|||Edmund,
To truly remove time from datetime and not just "hide" it:
SELECT CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME)
Returns:
2006-06-06 00:00:00.000
Mark|||A slightly better way to do it than using the CONVERT() function calls
would be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d,
DATEDIFF(d, 0, T2.Date), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web
article about datetime data including a short section on stripping time
info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
*mike hodgson*
http://sqlnerd.blogspot.com
Barry wrote:

>Hi,
>Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
>(Varchar(13), T2.Date, 112)
>
>HTH
>Barry
>
>|||Mike, this is very good.
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:u4N4C8PiGHA.1612@.
TK2MSFTNGP04.phx.gbl...
A slightly better way to do it than using the CONVERT() function calls would
be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d, DATEDIFF(d, 0, T2.Da
te), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web article a
bout datetime data including a short section on stripping time info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
mike hodgson
http://sqlnerd.blogspot.com
Barry wrote:
Hi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry

How to remove tabs from a field?

Is there any way to remove tabs from text fields? I see tabs at the end when I import the data in Excel.
Appreciate any helpSure, in the view that you are extracting use the Replace function, something like:CREATE VIEW vThingie
SELECT thingieId, Replace(thingieText, Char(9), ' ') AS d
FROM thingie
WHERE thingieId < 100...or something like that!

-PatP

How to Remove Carriage Returns from database field?

I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks

How to Remove Carriage Returns from database field?

I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks

How to Remove Carriage Returns from database field?

I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks

How to remove a ' from a field

I need help removing a ' from a field
Here is an example that should get you started:
create table #sometable (somecolumn varchar(80))
insert into #sometable (somecolumn) values ('lets try to remove the '' =
additional text'' within this column')
insert into #sometable (somecolumn) values ('lets try to remove the '''' =
additional text'''' within this column')
select * from #sometable=20
GO
UPDATE #sometable SET somecolumn =3D REPLACE(somecolumn, '''', '')
select * from #sometable=20
--=20
Keith
"Jon Hirasaki" <jhirasaki@.homecaresupply.com> wrote in message =
news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...
> I need help removing a ' from a field
|||I used the update command
update table set name = replace (name,'''','')
and it puts a blank space in the field where the ' was. How do I remove the blank space? I other blank spaces in the field that I need to keep.
"Keith Kratochvil" wrote:

> Here is an example that should get you started:
>
> create table #sometable (somecolumn varchar(80))
> insert into #sometable (somecolumn) values ('lets try to remove the '' additional text'' within this column')
> insert into #sometable (somecolumn) values ('lets try to remove the '''' additional text'''' within this column')
> select * from #sometable
> GO
> UPDATE #sometable SET somecolumn = REPLACE(somecolumn, '''', '')
> select * from #sometable
>
> --
> Keith
>
> "Jon Hirasaki" <jhirasaki@.homecaresupply.com> wrote in message news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...
>
|||I am not sure what you mean. The update sets this value:
lets try to remove the ' additional text' within this column
To this:
lets try to remove the additional text within this column
As you can see, the apostrophe after "text" has been removed and =
replaced with nothing. There is still one space between "text" and =
"width."
The apostrophe after "the" was removed and replaced with nothing, =
however there are two spaces between "the" and "additional" because =
there were two spaces between the original string "the ' additional."
The update works as expected...the layout of the original data causes =
the end result to look a bit strange.
Do you have two spaces when you only want one? If so, try this:
UPDATE #sometable SET somecolumn =3D REPLACE(somecolumn, ' ', ' ')
select * from #sometable=20
--=20
Keith
"Jon Hirasaki" <Jon Hirasaki@.discussions.microsoft.com> wrote in message =
news:D804BB8F-C783-4D63-8DF0-0EC2BBAE69FE@.microsoft.com...
> I used the update command
> update table set name =3D replace (name,'''','')
> and it puts a blank space in the field where the ' was. How do I =
remove the blank space? I other blank spaces in the field that I need =
to keep.[vbcol=seagreen]
>=20
> "Keith Kratochvil" wrote:
>=20
'' additional text'' within this column')[vbcol=seagreen]
'''' additional text'''' within this column')[vbcol=seagreen]
news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...[vbcol=seagreen]
|||What I have is value: let's try to remove the additional text within this column
I run: update #sometable set somecolumn= replace(somecolumn, '''', ' ')
This code removes the ' but it leave a blank space so the value look like this:
let s try to remove the additional text within this column
"Keith Kratochvil" wrote:

> I am not sure what you mean. The update sets this value:
> lets try to remove the ' additional text' within this column
> To this:
> lets try to remove the additional text within this column
> As you can see, the apostrophe after "text" has been removed and replaced with nothing. There is still one space between "text" and "width."
> The apostrophe after "the" was removed and replaced with nothing, however there are two spaces between "the" and "additional" because there were two spaces between the original string "the ' additional."
> The update works as expected...the layout of the original data causes the end result to look a bit strange.
> Do you have two spaces when you only want one? If so, try this:
> UPDATE #sometable SET somecolumn = REPLACE(somecolumn, ' ', ' ')
> select * from #sometable
> --
> Keith
>
> "Jon Hirasaki" <Jon Hirasaki@.discussions.microsoft.com> wrote in message news:D804BB8F-C783-4D63-8DF0-0EC2BBAE69FE@.microsoft.com...
>
|||Try removing the space from the "replace" character (as I had done =
within my original sql statement):
update #sometable set somecolumn=3D replace(somecolumn, '''', '')
--=20
Keith
"Jon Hirasaki" <Jon Hirasaki@.discussions.microsoft.com> wrote in message =
news:F41CC89E-0926-4D24-B624-1052A48DD57B@.microsoft.com...
> What I have is value: let's try to remove the additional text within =
this column
> I run: update #sometable set somecolumn=3D replace(somecolumn, '''', ' =
')
> This code removes the ' but it leave a blank space so the value look =
like this:[vbcol=seagreen]
> let s try to remove the additional text within this column
>=20
>=20
>=20
> "Keith Kratochvil" wrote:
>=20
replaced with nothing. There is still one space between "text" and =
"width."[vbcol=seagreen]
however there are two spaces between "the" and "additional" because =
there were two spaces between the original string "the ' additional."[vbcol=seagreen]
causes the end result to look a bit strange.[vbcol=seagreen]
message news:D804BB8F-C783-4D63-8DF0-0EC2BBAE69FE@.microsoft.com...[vbcol=seagreen]
remove the blank space? I other blank spaces in the field that I need =
to keep.[vbcol=seagreen]
the '' additional text'' within this column')[vbcol=seagreen]
the '''' additional text'''' within this column')[vbcol=seagreen]
'')[vbcol=seagreen]
news:7CBE1935-9FD2-474F-9C19-99AAB3A3736E@.microsoft.com...[vbcol=seagreen]
sql

How to reference a previous field Alias in TSQL as in Jet SQL?

My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'

SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable

The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:

A B C
1 2 3

However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?

The Jet syntax is non-standard SQL syntax so it will not work in SQL Server. You will have to use a derived table or CTE (in SQL Server 2005) or use a view or computed column in the table (if the expression involves columns from a single table).

select A, B, A+B as C

from (select 1 as A, 2 as B

from sometable

) as t

with t as (

select 1 as A, 2 as B

from sometable

)

select A, B, A+B as C

from t

How to refer to calculated field ?

When I do the following sql statement, I got the error "Invalid column name
'buy'."
SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
Ddif = case when [buy]=0 then 1 else 2 end --> error here
FROM tbl1
How can I refer to Buy in the same sql statement ?
Thanks.That is not a calculated field, it is a column alias. You cannot refer it in
other clauses in the same select statement unless it is nested in a derived
table. Your options are:
SELECT col,
< expression using col >
FROM ( SELECT CASE WHEN col = 'buy'
THEN 1 ELSE 2
END
FROM tbl ) t ( col ) ;
You can also reuse the same expression to avoid the derived table construct
as well.
Anith|||You can't. You'll have to use a derived table. Something like:
select DerivedTable.Buy
,Ddif = case when DerivedTable.Buy=0 then 1 else 2 end
from (
SELECT Buy = case when [col1]='BUY' then 1 else 2 end
FROM tbl1
) DerivedTable
Of course this is untested.
ML
http://milambda.blogspot.com/|||Your first 'BUY' statement is pretty simple - just 1 or 2 based on col1 =
BUY, so how about :
SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
Ddif = case when [col1]<>'BUY' then 1 else 2 end
FROM tbl1
Or something like that. In other words, build the logic for Ddif back to
the source column unaltered by the first case statement.
However, there may be a problem in your logic in terms of what happens first
and second, etc. See what you think:
If you are saying:
For COL1 if 'BUY' then 1 else 2
then after that is done there will be 0 rows where BUY = 0, the only options
are 1 or 2.
So the dDif column will always be 2 no matter what.
"fniles" wrote:
> When I do the following sql statement, I got the error "Invalid column nam
e
> 'buy'."
> SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
> Ddif = case when [buy]=0 then 1 else 2 end --> error here
> FROM tbl1
> How can I refer to Buy in the same sql statement ?
> Thanks.
>
>|||Thank you everybody for your help.

> You can also reuse the same expression to avoid the derived table
> construct
How can I reuse the same expression ? In my case, I will need to reuse the
column alias in more than 1 places in the same sql statement.
For ex:
SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
ABC = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OamRSFf%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> That is not a calculated field, it is a column alias. You cannot refer it
> in other clauses in the same select statement unless it is nested in a
> derived table. Your options are:
> SELECT col,
> < expression using col >
> FROM ( SELECT CASE WHEN col = 'buy'
> THEN 1 ELSE 2
> END
> FROM tbl ) t ( col ) ;
> You can also reuse the same expression to avoid the derived table
> construct as well.
> --
> Anith
>|||I am wondering since I will do this in a stored procedure and I need to use
the derived column more than once in the same sql statement, would it be
possible to use a temporary table for the derived column, and if it is how
can I do that ? Thanks.
The sql statement:
SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
ABC = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>'0'))
"fniles" <fniles@.pfmail.com> wrote in message
news:%23SD3Bsn%23FHA.360@.TK2MSFTNGP09.phx.gbl...
> Thank you everybody for your help.
>
> How can I reuse the same expression ? In my case, I will need to reuse the
> column alias in more than 1 places in the same sql statement.
> For ex:
> SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
> ABC = case when BUY>0 then
> (
> Left(BUY,charindex('.',BUY)-1)*32
> +
> substring(BUY,charindex('.',BUY)+1,2)*2
> +
> case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
> )
> else Null
> end
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OamRSFf%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>|||Please post a simplified version of your table schema with a few sample data
& expected results. Include a brief explanation of what you are trying to
accomplish here.
Based on the code snippet you posted, it looks mostly like some formatting
issue that, in general, can be handled differently, perhaps even outside the
database server.
Anith|||I am actually trying to convert Access db to SQL Server, and the query I
posted was a part of a longer query that I need to convert.
CREATE TABLE tblA (
Price float NULL,
CC varchar(50) NULL,
Account varchar(50) NULL,
Quantity smallint NULL,
:
)
CREATE TABLE tblB (
CC varchar(50) NULL,
:
)
CREATE TABLE tblC (
Account varchar(50) NULL,
:
)
SELECT LEFT(convert(varchar(8),Price),8)+'0000'
,8) AS BUY,
HClose = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>'0'))
Example:
tblA
Price CC Account
6 1212.25 ES 12345
6 0 ES 12345
1 1585.5 NQ 12345
1 51 NQ 12345
tblB
Symbol
ES
NQ
tblC
Account
12345
Result:
Buy HClose Account
ES 1212.2500 77618 12345
ES 0.0000 12345
NQ 1585.5000 101540 12345
NQ 51.0000 3352 12345
I am also having a problem converting a floating number to a 4 decimal
floating number: ex: from 1212.25 to 1212.2500, 0 to 0.0000, etc.
In my Stored Procedure, can I just derived the column Buy in a temp table,
and refer it in the next sql statement ?
Thanks.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Please post a simplified version of your table schema with a few sample
> data & expected results. Include a brief explanation of what you are
> trying to accomplish here.
> Based on the code snippet you posted, it looks mostly like some formatting
> issue that, in general, can be handled differently, perhaps even outside
> the database server.
> --
> Anith
>|||Just to add, the derived column HClose is also going to be used again in the
same query.
This is from the Access query:
IIf([buy]=0,[Hprice]-[HCLOSE],[HCLOSE]-[Hprice]) AS Hdif
"fniles" <fniles@.pfmail.com> wrote in message
news:etFhZ7o%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am actually trying to convert Access db to SQL Server, and the query I
>posted was a part of a longer query that I need to convert.
> CREATE TABLE tblA (
> Price float NULL,
> CC varchar(50) NULL,
> Account varchar(50) NULL,
> Quantity smallint NULL,
> :
> )
> CREATE TABLE tblB (
> CC varchar(50) NULL,
> :
> )
> CREATE TABLE tblC (
> Account varchar(50) NULL,
> :
> )
> SELECT LEFT(convert(varchar(8),Price),8)+'0000'
,8) AS BUY,
> HClose = case when BUY>0 then
> (
> Left(BUY,charindex('.',BUY)-1)*32
> +
> substring(BUY,charindex('.',BUY)+1,2)*2
> +
> case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
> )
> else Null
> end
> from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
> tblA.Account = tblC.Account
> WHERE (((tblA.Quantity)<>'0'))
> Example:
> tblA
> Price CC Account
> 6 1212.25 ES 12345
> 6 0 ES 12345
> 1 1585.5 NQ 12345
> 1 51 NQ 12345
> tblB
> Symbol
> ES
> NQ
>
> tblC
> Account
> 12345
>
> Result:
> Buy HClose Account
> ES 1212.2500 77618 12345
> ES 0.0000 12345
> NQ 1585.5000 101540 12345
> NQ 51.0000 3352 12345
>
> I am also having a problem converting a floating number to a 4 decimal
> floating number: ex: from 1212.25 to 1212.2500, 0 to 0.0000, etc.
> In my Stored Procedure, can I just derived the column Buy in a temp table,
> and refer it in the next sql statement ?
> Thanks.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>|||This is a part of the original Access query that I need to convert to SQL
Server:
SELECT Format([tblA].[PRICE],"0.0000") AS BUY,
IIf(Val([BUY])>0,(Left$([BUY],InStr([BUY],".")-1)*32+Mid$([BUY],InStr([BUY],".")+1,2))*2+(IIf(Mid$([BUY],InStr(
[BUY],".")+3,1)=5,1,0)),Null)
AS HClose,
IIf([buy]=0,1-[HCLOSE],[HCLOSE]-1) AS Hdif,...
FROM (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>"0"));
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Please post a simplified version of your table schema with a few sample
> data & expected results. Include a brief explanation of what you are
> trying to accomplish here.
> Based on the code snippet you posted, it looks mostly like some formatting
> issue that, in general, can be handled differently, perhaps even outside
> the database server.
> --
> Anith
>sql

How to read XML data from SQL data field?

Using RS2000

I have a field in a SQL DB which contains data stored as an XML string. Is there an easy way to parse this string into data without moving to SS2005?

I have builts a parser in SQL, but I would like to know if RS had a way to directly read this. If not, is there a method in RS2005 which handles this type of data?

The only way to really do this in RS2000 is to create your own custom data processing extension that encapsulates the code you have written to parse the Xml. There are quite a few examples online.

In RS2005, you can use the Xml Data Processing Extension, which is designed for retieving data from xml data sources. It retrieves the data from an xml document which is retireved from a web service, web page, or can use a document embedded in the Xml Query. This extension is very powerful, but can also be somewhat complex for advanced queries.

For more information: http://msdn2.microsoft.com/en-us/library/ms159741.aspx|||

you can write a function in report Code section that will take the xml string, load it into System.Xml.XmlDocument and parse it.

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>server=.</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>6cfe005e-c5e0-4297-aa6b-f2ca0673ec05</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ReportItems>

<List Name="list1">

<Left>1.25in</Left>

<ReportItems>

<Textbox Name="my_xml_1">

<Left>0.5in</Left>

<Top>0.5in</Top>

<rd:DefaultName>my_xml_1</rd:DefaultName>

<Width>3.375in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>=Code.ParseXml(Fields!my_xml.Value)</Value>

</Textbox>

</ReportItems>

<DataSetName>DataSet1</DataSetName>

<Top>0.375in</Top>

<Height>1in</Height>

</List>

</ReportItems>

<Height>2in</Height>

</Body>

<rd:ReportID>c9ad02b9-b7a2-4780-b954-3ebaa11e0afb</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select '&lt;root&gt;Hello world!&lt;/root&gt;' my_xml

union select '&lt;root&gt;Goodbye!&lt;/root&gt;'</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="my_xml">

<rd:TypeName>System.String</rd:TypeName>

<DataField>my_xml</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<CodeModules>

<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>

</CodeModules>

<Code>public shared Function ParseXml(xml as string) as string

Dim doc as System.Xml.XmlDocument = new System.Xml.XmlDocument()

doc.LoadXml(xml)

ParseXml = doc.SelectSingleNode("root").InnerText

end function</Code>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

How to Query... Please help

hi friends,

I have a table whose one field is 'monthname' containing month names. I want to get these month names as field values with an SQLQry. no Stored procedures or functions can be used.

Normal Result: (Select monthname Month,Count(ID) Count From TestData Group By Monthname)

MonthCountJan2Feb7Mar3Apr4May5Jun2Jul2Aug4Sept5Oct6Nov4Dec4

My Required Format:

IDJanFebMarAprMayJunJulAugSeptOctNovDec1231234888665555

(this month name is just an example, it can be anything. like year or Flight Name. I mean i cant hard code like 'Select * From TestData where monthName = 'Jan'')

Got me...?

Thanking,

:)

Search these forums for pivot. Or advanced search where I say something about pivot in this forum.

How to Query to get the Numeric fields Only in the Table

hi Good Day everyone.

how can i query numeric values in the specific fields for example EmployeeID only.

but the field EmployeeID has a datatype of varchar.

for example :

EmployeeID -> Field

001

002

a

b

c

how do i query 001 and 002 only. tnx

here it is,

Code Snippet

Create Table #employee (

[EmployeeID] Varchar(100)

);

Insert Into #employee Values('001');

Insert Into #employee Values('002');

Insert Into #employee Values('a');

Insert Into #employee Values('b');

Insert Into #employee Values('c');

Insert Into #employee Values('1E2');

Insert Into #employee Values('.');

Insert Into #employee Values('$');

Insert Into #employee Values('E0');

Select

*

from

#employee

Where

Isnumeric([EmployeeID]) = 1

And [EmployeeID]not like '%[^0-9]%'

|||Thanks Manivannan.D.Sekaran it helps me a lot.

How to Query Question

I have a table of Salesmen and as a child table, with PK FK constraint, the
OrdersBySalesmen with a DateOrdered field in the latter.
Table Salesmen
Id Type IntName nvarchar(50)
Table OrderBySalesmen
Id Type Int
SalesmenId TypeInt
DateOrdered Type DateTime
MyQuestion is a s follows.
I need to obtain as a query result the last (most recent) 10 sales records
for each salesman.
Can anyone tell me how this query would be made, If I do a select TOP 10
with innner joints order by dateOrdered DESC I only get 10 records, not the
top 10 for EVERY salesman.
Any help would be greatly appreciated.
RDTry,
select SalesmenId, [Id], DateOrdered
from OrderBySalesmen as a
where [id] in (select top 10 [id] from OrderBySalesmen as b where
b.SalesmenId = a.SalesmenId)
AMB
"RD" wrote:

> I have a table of Salesmen and as a child table, with PK FK constraint, th
e
> OrdersBySalesmen with a DateOrdered field in the latter.
> Table Salesmen
> Id Type IntName nvarchar(50)
> Table OrderBySalesmen
> Id Type Int
> SalesmenId TypeInt
> DateOrdered Type DateTime
> MyQuestion is a s follows.
> I need to obtain as a query result the last (most recent) 10 sales records
> for each salesman.
> Can anyone tell me how this query would be made, If I do a select TOP 10
> with innner joints order by dateOrdered DESC I only get 10 records, not th
e
> top 10 for EVERY salesman.
> Any help would be greatly appreciated.
> RD
>
>|||Correction,
select SalesmenId, [Id], DateOrdered
from OrderBySalesmen as a
where [id] in (select top 10 [id] from OrderBySalesmen as b where
b.SalesmenId = a.SalesmenId
order by DateOrdered desc
)
AMB
"Alejandro Mesa" wrote:
> Try,
> select SalesmenId, [Id], DateOrdered
> from OrderBySalesmen as a
> where [id] in (select top 10 [id] from OrderBySalesmen as b where
> b.SalesmenId = a.SalesmenId)
>
> AMB
> "RD" wrote:
>

How to query COUNT

If I want to find out how many items of a field is in a table like Type1, Type2... to build a table like

Type1, 10
Type2, 25
...

Is is better to open the table -> Select Type From Table

and then go down each row and record the count of a type or is it better to

Select Distinct Type from table to get the types and then do each type's select to get the count

Select Count (1) from table where type = 'Type?'

Thanks,

FrankUse COUNT and GROUP BY

How to query by keyword for image field.

If the data type of field is "varchar",we can use "like" to query if it has
some substring.
Such as "where custom.valuevariant like '%Verizon%' ", it will query out all
records that contains string "Verizon".
But how to do when data type of field custom.valuevariant is "image"?
ThanksIt would depend what type of data you've stored in the image column,
but full-text search supports querying text and Office (Word etc.
documents) data in an image column - see "Filtering Supported File
Types" in Books Online.

Simon|||Thank you very much.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1122017552.084995.312000@.f14g2000cwb.googlegr oups.com...
> It would depend what type of data you've stored in the image column,
> but full-text search supports querying text and Office (Word etc.
> documents) data in an image column - see "Filtering Supported File
> Types" in Books Online.
> Simon

How to query a number (street number)...

I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...|||If your street number column is a string, just use the LIKE operator.
If it is not a string, then it should be. Change it.

Rule Of Thumb: If you don't add it, subtract it, multiply it, or divide it, then it is not a number even if it looks like one.|||DECLARE @.MyChar9 CHAR(9)
SET @.MyChar9 =' 20'
SELECT @.MyChar9,LEFT(LTRIM(@.MyChar9),1)
WHERE LTRIM(@.MyChar9) LIKE '2%'


GW|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.|||Correct me if I'm wrong but...
Using LTRIM and LIKE will ignore any indexes.I cannot because you are not :)

@.OP - simpler just to write
...
@.MyChar9 LIKE ' 2%'
No indexes used there either but less RSI from all that typing.|||... unless of course there might not be leading spaces in which case ignore.|||... unless of course there might not be leading spaces in which case ignore.
In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().|||In a right justified char(9) column there may be up to 8 leading spaces. Hence Gwilly's LTRIM().But there might be none which was why I said ignore my solution (which is likely wrong).|||LIKE will like totally use the index, if the wildcard comes at like the end or whatever. So like this will do an index seek:

where column like 'abcd%'

But this will like totally gag your server:

where column like '%wxyz'

It's like when you wanna text your friend, and you like only know their last name or whatever, you have to go through each one, since the index is ordered by like the first name, you know.

OK. I will go back to English, now ;-)|||' 200'
' 21'
' 2005'
sorry for confusion just been a long hard day...

How is a char(9) field right justified?