help! trying to find records inside text string

Hi, got a problem and can't figure this one out.

basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...

record 1 : (01)
record 2 : (02)
etc...

and i want to compare this one field to those records to see whether that text string contains anything the table contains.

i've tried ContainsTable & IN but still can't figure this out.

any help greatly appreciated
:owould you kindly give us the table names and column names involved

otherwise the most i can tell you is to use LIKE

Help! Parameter Conversion Unix Time

My dates are in unix time and the paramaters are entered as 7/6/2004. How can I convert the date in the parameter field back into unix time so it will retireve the records.
Unix time is the number of seconds from 1/1/1970 at 12:00am
ThanksFor a variable dateTime of type DateTime you can use the follwing
expression:
dateTime.Subtract(new DateTime(1970, 1, 1)).TotalSeconds
This returns floating point value and may contain fractions of a second.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:52981B6F-5FB5-4638-8959-8CFE298D5503@.microsoft.com...
> My dates are in unix time and the paramaters are entered as 7/6/2004. How
can I convert the date in the parameter field back into unix time so it will
retireve the records.
> Unix time is the number of seconds from 1/1/1970 at 12:00am
> Thanks

Help! Need to extract date/time from SQL database table

Please help! How do I extract mm/dd/yyyy hh:nn from a timestamp field located in an MS SQL 2000 database table?

I can do this in MS Access where , for example only, I can convert 56323938 to "07/24/2006 11:47"

But when i attemp to extract the same info from the same field in SQL Query Analyzer, I only get "07/24/2006 00:00".

Can someone help me ? Is there a stored procedure or sql string that I can use that will in fact return the "mm/dd/yyyy hh:nn" from a timetamp field (where the hh:nn is actual and not fillers like "00:00"?

:)Dear,
The ODBC and OLE DB timestamp data type records dates and times. The Transact-SQL timestamp data type is a binary data type with no time-related values.
May You get some idea from this.otherwise there should be no problem in retrieving time through (select <date_column> from <table>),i suppose.
Regards,
Lipsa|||Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg|||I now recall how i got the mm/dd/yyyy hh:mm am/pm from a timestamp

I used the following string:

select convert(datetime,cast( [timestamp] as decimal)/1440 + 1,101) from table

(timestamp is the sql encoded stamp integer field stored on a table. By converting the [timestamp] field to decimal instead of "datetime" allows the hh:mm:ss to be extracted where the "/ 1440 + 1" extracts the date from the timestamp.

:)

Quote:

Originally Posted by MikeG

Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg

Help! Im using MS SQL Views

And I need help with a query. I have a field that I want to test if it has ".bat" at the end if so I want to replace this with another field. I tried using the "replace"
but it replaces only the ".bat" portion of the field. Any idea's?

ThanksSo you want to replace the whole entire field with another field where the field has .bat?
How about
update Table1
set field1 = field2 where field1 like '%.bat'

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegroups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
---
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
---
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegroups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> ---
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> ---
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>|||Thanks Tibor! That was helpful.

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the valu
e 0 is the same as the
value 0.00. The client application presents the binary values returned by SQ
L Server in a
human-readable form (like number with decimal points). If you didn't write t
he client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Serv
ices, etc, then these
tools will present the values according to the datatype *for the column'. I.
e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the
person writing the too
who makes these decision, but it applies to all values for the column. The o
nly way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to
some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to ha
ndle this in the client
app. All client app designed for end users has functionality for formatting
data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?
Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegro ups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>
|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.
|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.
|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegr oups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>
|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.
|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegr oups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>
|||Thanks Tibor! That was helpful.

Help! - Query On Time Field In SQL Server - How?

Hi,

I have an orders database and I need to be able to write a query
that groups sales by:

1. Date
2. Time of day (both am and pm)

I currently have a date/time field named "Submitted" that stores
just the date.

However, how do I store the time?

I know I can store the date/time in the "Submitted" field I
created. However, because of the time being stored in that field, I
can't execute an easy query such as:

select * from database where submitted = '2/10/05'

(reason being, the time portion makes each "Submitted" field
record unique, so just the date won't be recognized)

I realize there probably is a much better way to do this in the
SQL query language that I am not aware of.

Two questions:

1. Based on what I'm trying to do -- query based on date and group
by number of sales for each hour of the day (am and pm) -- what is the
best approach to store the time?

2. What does the query look like that querys by date and groups
orders by time of day (am and pm)?

Thanks for your help.

JThe examples below should help. Notice the Y-M-D formats I have used
for date literals. These formats are safe under any regional connection
settings whereas other formats used in code (such as in the example you
gave) may fail depending on the settings of DATEFORMAT and LANGUAGE.

CREATE TABLE YourTable (dt DATETIME PRIMARY KEY)

INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000')
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000')

/* Retrieve a particular date */
SELECT *
FROM YourTable
WHERE dt >= '20050210'
AND dt < '20050211'

/* Group by date and hour */
SELECT MIN(dt), COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(HH,'20000101',dt)

--
David Portas
SQL Server MVP
--

Help withrounding money to 2 decimal places?

I have a table with a money field that had previously been running calculation and storing the data into the database's money field. Since this field supports 4 decimal places, it was storing 4 decimal places worth of data. I have since cleaned up my insert routine to round everything up to two decimal places and it only inserts the rounded values. I now have to go back and update the old data with the two decimal place rule. How would I go about doing this?

OLD--------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspx|||Nevermind, this was extremely easy ... perhaps I should have read the BOL first!

Update tblName
Set fldName = round(fldName, 2)|||How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspxThis isn't help. Granted, the question has an elementary solution; you could have simply stated such and gotten your point across. Thanks for the condescending insight to your personality :mad:|||Nevermind, this was extremely easy ... perhaps I should have read the BOL first!

Update tblName
Set fldName = round(fldName, 2)Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:Original -1.5 -0.5 0.5 1.5 2.5 3.5
T-SQL -2.0 -1.0 1.0 2.0 3.0 4.0
Correct -2.0 0.0 0.0 2.0 2.0 4.0-PatP|||Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:Original -1.5 -0.5 0.5 1.5 2.5 3.5
T-SQL -2.0 -1.0 1.0 2.0 3.0 4.0
Correct -2.0 0.0 0.0 2.0 2.0 4.0-PatPI'm using SQL Server 2000, which requires that when using the Round() function that you use the length argument. Not sure about previous SQL implimentations.

Syntax
ROUND ( numeric_expression , length [ , function ] )

So, Round(1.5) will give an error because it requires 2 or 3 arguments. However, Round(1.5, 2) will give 1.5 whereas Round(1.5, 0) will return 2.0|||This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.

Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).

In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.

This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.

-PatP|||This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.

Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).

In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.

This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.

-PatPCorrect, I am using a money field and thus I need two decimal places and thus used the length argument of 2. Thanks for all the insight though ... much appreciated!

Help with XQuery

Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
--
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcomMarco Minerva wrote:

> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom

Help with XQuery

Hi all!
I have a SQL Server 2005 table with a XML field containing documents with
the following structure:
<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>
Via XQuery, it is possible to retrieve only the text that is contains within
a certain tag? For example, if I want the text that is surrounded by the "t"
tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in the same way,
the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT 3 TEXT 6".
Thanks in advance for the attentio.
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom
Marco Minerva wrote:

> I have a SQL Server 2005 table with a XML field containing documents
> with the following structure:
> <document>
> <title>Trittico di San Giovenale</title>
> <info>
> </info>
> <text>
> <ap>
> <e>
> <t>TEXT 1</t>
> TEXT 2
> <t>TEXT 3</t>
> </e>
> <t>TEXT 4</t>
> </ap>
> <ap>
> <t>TEXT 5
> <e>TEXT 6</e>
> </t>
> </ap>
> </text>
> </document>
> Via XQuery, it is possible to retrieve only the text that is contains
> within a certain tag? For example, if I want the text that is surrounded
> by the "t" tag, I must obtain "TEXT 1 TEXT 3 TEXT 4 TEXT 5 TEXT 6"; in
> the same way, the text surrounded by the "e" tag is "TEXT 1 TEXT 2 TEXT
> 3 TEXT 6".
Using e.g. //e//text() you can access all descendant text nodes so
DECLARE @.x XML;
SET @.x = '<document>
<title>Trittico di San Giovenale</title>
<info>
</info>
<text>
<ap>
<e>
<t>TEXT 1</t>
TEXT 2
<t>TEXT 3</t>
</e>
<t>TEXT 4</t>
</ap>
<ap>
<t>TEXT 5
<e>TEXT 6</e>
</t>
</ap>
</text>
</document>';
SELECT @.x.query('//e//text()') AS etext;
returns
TEXT 1
TEXT 2
TEXT 3TEXT 6
and
SELECT @.x.query('//t//text()') AS ttext;
returns
TEXT 1TEXT 3TEXT 4TEXT 5
TEXT 6
As you can see the query finds the right text nodes but includes white
space while you seem to want to strip white space but insert one space
between text nodes.
With XQuery 1.0 you could use the normalize-space and string-join
function but unfortunately the XQuery implementation in SQL server 2005
does not provide those functions.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OQpt96%23tHHA.4440@.TK2MSFTNGP06.phx.gbl...
> Marco Minerva wrote:
>
> Using e.g. //e//text() you can access all descendant text nodes so
>
> SELECT @.x.query('//e//text()') AS etext;
> returns
> TEXT 1
> TEXT 2
> TEXT 3TEXT 6
> and
> SELECT @.x.query('//t//text()') AS ttext;
> returns
> TEXT 1TEXT 3TEXT 4TEXT 5
> TEXT 6
>
> As you can see the query finds the right text nodes but includes white
> space while you seem to want to strip white space but insert one space
> between text nodes.
> With XQuery 1.0 you could use the normalize-space and string-join function
> but unfortunately the XQuery implementation in SQL server 2005 does not
> provide those functions.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Hi!
Thank you very much, it is what I was looking for!
Marco Minerva, marco.minerva@.gmail.com
http://blogs.ugidotnet.org/marcom

Help with varchar to date

I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived form the
column above. So I tried the following
CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
RETURNS smalldatetime
AS
BEGIN
--want to be sure it is interpreted as dd-mm-yy format
RETURN CONVERT(smalldatetime,
'01-' +
CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+ '-' + Right(@.DATETEXT, 2))
END
I then try to execute the following:
UPDATE Credit_Card
SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
WHERE LEN(Expiry_Date) = 5 --jic a bad field
And get the following error:
Syntax error converting character string to smalldatetime data type
The function does return correctly. Can anyone give me some help in getting
this working. ThanksCan you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure.
This is just one of the dozens of problems with choosing the wrong data
type. Another big one with your function specifically:
You're checking for left(@.datetext,2) but then saying WHEN 1 -- two problems
here, one is that 1 is not a string ('1' would be) and unless it is november
or december, I am sure that Left(@.DateText, 2) yields two characters (only
one of which is the month). How on earth do you distinguish between 11206
(jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)?
Again, some sample data that causes the problem would be useful. But more
importantly, before trying to debug a function, get a query running that
does what you want (but without the convert to smalldatetime). That makes
it much easier to debug and figure out which rows are not producing valid
dates.
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||Harry
> --want to be sure it is interpreted as dd-mm-yy format
Make sure that the format you are converting to is YYYYMMDD
CREATE TABLE Credit_Card (dt VARCHAR(5))
INSERT INTO Credit_Card SELECT '10/06'
SELECT * FROM Credit_Card
UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01'
AS
DATETIME)
It retruns just 'June'
How about to alter the table and change the datatype's column or expand it
to varchar(50) for instance
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Can you show a simplified example, e.g. your table structure, and 3 or 4
> rows of sample data that cause the failure.
> This is just one of the dozens of problems with choosing the wrong data
> type. Another big one with your function specifically:
> You're checking for left(@.datetext,2) but then saying WHEN 1 -- two
> problems
Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
'01'
The function DOES work correctly and returns from a parameter of '06/06'
the result '2006-06-01 00:00:00'
TRY the function!

> here, one is that 1 is not a string ('1' would be) and unless it is
> november or december, I am sure that Left(@.DateText, 2) yields two
> characters (only one of which is the month). How on earth do you
> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about
> 1006 (nov 06) vs. 106 (jan 06)?
READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
I am prepending '01-' for the day in the function.
The reason I am converting the LEFT 2 characters to a tinyint for use in the
CASE statement
e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
in dd-MMM-yy format
so the convert function will not be between an Australian date
format and a US format.

> Again, some sample data that causes the problem would be useful. But more
> importantly, before trying to debug a function, get a query running that
> does what you want (but without the convert to smalldatetime). That makes
> it much easier to debug and figure out which rows are not producing valid
> dates.
AGAIN, the function works - READ what I have written. Further to this I have
given you sample data!
"I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived from the
column above. So I tried the following"
PLEASE read and understand the question.

>
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>|||"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
Perhaps I had better explain further:
I have a table with a number of columns, one of which is called
"Expiry_Date" - varchar(5) which stores string values in the format mm/yy
e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I
now need to know in advance if a crediy card is going to expire. The current
format makes that very difficult. So I am trying the following:
I have added another column called "CardExpiry" which is smalldatetime. I
want to update this column from values contained in the "Expiry_Date"
column. Obviously I have to convert the string value to a smalldatetime
value first. That is why I created the function above. Even though the
function returns a smalldatetime value, sql server (2000) still thinks the
output of the function is a character string.|||> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)
INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06'
SELECT * FROM Credit_Card
--Now we are going to update CardExpiry column
UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT
(Expiry_Date,2) +'01' AS SMALLDATETIME)
SELECT * FROM Credit_Card
DROP TABLE Credit_Card
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:1Jqmg.14185$ap3.3358@.news-server.bigpond.net.au...
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
> I now need to know in advance if a crediy card is going to expire. The
> current format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>|||This alteration to your function shuold clear up the error you are receiving
.
It returns a alpha month, 4 digit year, and the first of the month. Bad and
non-confirming parameters will return NULL. You can then easily find the bad
data.
CREATE FUNCTION dbo.StringToDate
( @.DateText varchar(11) )
RETURNS datetime
AS
BEGIN
IF len( @.DateText ) < 5
RETURN NULL
SET @.DateText =
CASE left( @.DateText, 2 )
WHEN '01' THEN replace( @.DateText, '01/', '01/Jan/' )
WHEN '02' THEN replace( @.DateText, '02/', '01/Feb/' )
WHEN '03' THEN replace( @.DateText, '03/', '01/Mar/' )
WHEN '04' THEN replace( @.DateText, '04/', '01/Apr/' )
WHEN '05' THEN replace( @.DateText, '05/', '01/May/' )
WHEN '06' THEN replace( @.DateText, '06/', '01/Jun/' )
WHEN '07' THEN replace( @.DateText, '07/', '01/Jul/' )
WHEN '08' THEN replace( @.DateText, '08/', '01/Aug/' )
WHEN '09' THEN replace( @.DateText, '09/', '01/Sep/' )
WHEN '10' THEN replace( @.DateText, '10/', '01/Oct/' )
WHEN '11' THEN replace( @.DateText, '11/', '01/Nov/' )
WHEN '12' THEN replace( @.DateText, '12/', '01/Dec/' )
END
IF @.DateText LIKE '%/0%'
RETURN ( replace( @.DateText, '/0', '/200' ))
IF @.DateText LIKE '%/1%'
RETURN ( replace( @.DateText, '/1', '/201' ))
RETURN NULL
END
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616
@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
> hold credit card expiry dates in mm/yy format e.g 10/06
>
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
>
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
>
> END
>
> I then try to execute the following:
>
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
>
> And get the following error:
>
> Syntax error converting character string to smalldatetime data type
>
> The function does return correctly. Can anyone give me some help in gettin
g
> this working. Thanks
>
>|||Apparently the function does NOT work -you are getting an error!
I'm now.
Does the function NOT work properly and you are asking for our help,
OR
Does the function work properly and you are posting here for -what was that
reason again?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:Svqmg.14176$ap3.2372@.news-server.bigpond.net.au...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
> '01'
> The function DOES work correctly and returns from a parameter of '06/06'
> the result '2006-06-01 00:00:00'
> TRY the function!
>
> READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
> I am prepending '01-' for the day in the function.
> The reason I am converting the LEFT 2 characters to a tinyint for use in
> the CASE statement
> e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
> in dd-MMM-yy format
> so the convert function will not be between an Australian date
> format and a US format.
>
> AGAIN, the function works - READ what I have written. Further to this I
> have given you sample data!
> "I want to convert a varchar(5) field to smalldatetime. I have a column
> that
> hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived from the
> column above. So I tried the following"
> PLEASE read and understand the question.
>
>|||Could it be that you have bad data in your table, for example an expiry date
of 13/06?
Chris
"Still Love VB6" wrote:

> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
I
> now need to know in advance if a crediy card is going to expire. The curre
nt
> format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>
>|||> Perhaps I had better explain further:
Yes, that would be a good start!

> Even though the function returns a smalldatetime value, sql server (2000)
> still thinks the output of the function is a character string.
No, that is not what is happening at all.
You have some "dates" in your table where they aren't really dates. I can
think of hundreds of examples, since you allow varchar(5) in there, there is
no easy way to make them conform to any date format, so your table is
probably full of crap. It may be one row that is causing your function to
fail; it may be all rows! Who knows?
Do you see, now, the importance of sample data!?

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with UPDATE trigger

I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain.com',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain.com',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email serverl'
select RC = @.rc
END

goYou need to check for the updated value.|||I'm sorry, but I am new to SQL.
Where and how do I insert CHECK|||Got it.

I added an IF
Here is the code I have if anyone needs

-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[reviewntc]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [reviewntc]
GO

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
set nocount on
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
IF @.CDStatus = 'In Review'
BEGIN
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email server'
select RC = @.rc

END
END
go

help with update query needed

Could anyone help me with an update query? I need to populate Table 1
(bookings) with data from Table 3 (defaults), via a joining field in Table 2
(enquiries). All fields are of type INT, using SQL Server 2000.
Table 1 (bookings):
id, t_val, q_val
Table 2 (enquiries):
id, booking_id, enq_type
Table 3 (defaults):
id, def_enq_type, def_t_val, def_q_val
Table 3 data:
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
The fields to be updated are 'bookings.t_val' and 'bookings.q_val', from
'defaults.def_t_val' and 'defaults.def_q_val' respectively. 'bookings.id'
relates to 'enquiries.booking_id', and 'enquiries.enq_type' to
'defaults.def_enq_type'. Just to make it more complex, 'booking_id' in
enquiries can have duplicates, in which case I want the one with the highest
'enquiries.id' (ie the most recent record).
Any help very gratefully received, I've been staring at it for hours.Tony,
Do you have any DDL for the Tables? And also some sample data for the
Tables?
Any how do you perceive Table 1(Bookings) to look like?
Thanks
Barry|||Try This:
declare @.bookings table(id int, t_val int, q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1, 1, 9)
insert @.enquiries values(2, 2, 11)
insert @.enquiries values(3, 1, 10)
insert @.bookings
select e.booking_id, d.def_t_val, d.def_q_val
from @.enquiries e
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
select * from @.bookings|||Thanks for the replies so far, but I'm still struggling.
I think I should have made it clearer that the table 'bookings' is already
populated with data, and that it is just 2 new columns (t_val, q_val) that
need populating (so obviously I've omitted all other fields not needed for
this update query).
So if 'bookings' currently looks like:
id, t_val, d_val
1, null, null
2, null, null
3, null, null
4, null, null
and 'enquiries' looks like:
id, booking_id, enq_type
1,2,9
2,4,10
3,1,12
4,1,11
5,3,9
(note more than one record for booking_id 1)
and as mentioned before, 'defaults' looks like:
id, def_enq_type, def_t_val, def_q_val
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
then the end result for 'bookings' should be:
id, t_val, d_val
1, 3, 2
2, 2, 1
3, 2, 1
4, 2, 2
Again, any help very gratefully received.
"JeffB" wrote:

> Try This:
> declare @.bookings table(id int, t_val int, q_val int)
> declare @.enquiries table(id int, booking_id int, enq_type int)
> declare @.defaults table(id int, def_enq_type int, def_t_val int,
> def_q_val int)
> insert @.defaults values(1,9,2,1)
> insert @.defaults values(2,10,2,2)
> insert @.defaults values(3,11,3,2)
> insert @.defaults values(4,12,1,2)
> insert @.enquiries values(1, 1, 9)
> insert @.enquiries values(2, 2, 11)
> insert @.enquiries values(3, 1, 10)
> insert @.bookings
> select e.booking_id, d.def_t_val, d.def_q_val
> from @.enquiries e
> inner join @.defaults d
> on e.enq_type = d.def_enq_type
> inner join (select booking_id, max(id) maxid from @.enquiries group by
> booking_id) x
> on e.booking_id = x.booking_id and e.id = x.maxid
> select * from @.bookings
>|||Try this:
declare @.bookings table(id int, t_val int, q_val int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
insert @.bookings values(1, null, null)
insert @.bookings values(2, null, null)
insert @.bookings values(3, null, null)
insert @.bookings values(4, null, null)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1,2,9)
insert @.enquiries values(2,4,10)
insert @.enquiries values(3,1,12)
insert @.enquiries values(4,1,11)
insert @.enquiries values(5,3,9)
update b
set b.t_val = d.def_t_val,
b.q_val = d.def_q_val
from @.bookings b
inner join @.enquiries e
on b.id = e.booking_id
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
where b.t_val is null and b.q_val is null
select * from @.bookings

help with update query

Hello all,

I have a database with table "A1" of fields
1. Id (Auto number)
2. Server(this matches the name field of A2)
3.Vendor
4.Model
5.ttyport
This table has 200 rows in it

and 2nd table A2 of fields

1.name(this matches the server field of A1)
2.ttyport
This has 100 rows in it.

I want to accomplish something like this,

Update A1 set A1.ttyport=A2.ttyport,A1.ttyport=A2.ttyport, where A1.Server=A2.name

if A1.Server IS NOT EQUAL A2.name then create another row in A1 with A2.name, A2.ttyport and leaving the rest of the fields with "null"

is it possible? can some one help me with the modification of the query ?

please help,
ThanksIf you want to do it using only one query, I'd say you can't do that - as far as I know, there's no way to do UPDATE and INSERT in the same statement.

You'll have to do the UPDATE with the one you already have (why do you have double "A1.ttyport=A2.ttyport" in it?) and write another one to INSERT data into A1 table.|||First update, than insert

update A1
set ttyport = (
select A2.ttyport
from A2
where A1.Server=A2.name
)
where exists (
select A2.ttyport
from A2
where A1.Server=A2.name
)

You can ommit where clause from update if you know that every record in A1 has 1 record in A2.

insert into A1 (servername, ttyport)
select name, ttyport
from A2
where not exists (
select A2.ttyport
from A2
where A1.Server=A2.name
)

I do not know what db and version you are on, but DB2 and Oracle have MERGE command which allows to do both operations in a single SQL.