Problems converting varchar to smallmoney

Hi

My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.

The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)

As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.

What do i have to do to convert the value as it is entered by the user?

Thanks in advance

t.

Statement:

INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()

DDL for concerned database:

CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

I am little confused. In your INSERT INTO statement, you are converting {3} and {4} to numeric, while the datatype for these two columns are smallmoney. Why don't you use smallmoney directly in the INSERT statement?

The reason it rounds up for you is that the default scale (max number of decimal digits) of numeric data type is 0. It would work fine if you use numeric(20, 2) for example. See "decimal and numeric (Transact-SQL)" in SQL Server Book Online for details.

|||

thx

seems to work now using smallmoney directly or numeric (20,2)

problems converting dates as varchar to datetime data type

Hi all,

I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.

I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.

Someone can help me? PLEASE

Thanks

In what format do you have your dates at the moment?

You can try using SET DATEFORMAT dmy before running the statment.

Or look at using CONVERT with the relevant style.

eg CONVERT(DATETIME, YourDateField, 103)

Check Books Online for more details

HTH!

|||Try CONVERT function, make sure the style parameter matches your varchar date format
|||

If your date is in a recognizable format SQL Server uses an implicit conversion from varchar to datetime, so no CONVERT or CAST function is necessary. If the date is in an unrecognizable format you can use the T-SQL string functions (listed in BOL) to reconstruct it into a recognizable format and you should get no errors.

|||

Hi,

I will post some more information about my problem.

In the source DB i have the "Beginning_Date" column, as varchar, with values with this format "dd/mm/yyyy HH.MM.SS".

In the target DB i have the "Beginning_Date" column again, but must be only with the the date part (dd/mm/yyyy) as datetime type, and the "Beginning_Time" column, storing the time part (HH:MMS) as nchar type.

I have no problems with the time part of the records, but the date part always fail.

I use the substring function in order to get only the date part of the records and try to insert it to the target DB both with "dd/mm/yyyy" and "mm/dd/yyyy" date formats. I always get the same error (out of range). I have tried also the "set datetime dmy" .......

Thanks for your help,

Bob

|||
Are all your dates in Beginng_Date in the same format? Are you sure there isn't a rogue value in there which is causing the whole statement to crash?

Can you post your DDL and DML statements and some sample data for us to help troubleshoot further?

I had a guess at what you were trying to do:

Code Snippet

CREATE TABLE tblDateTime
(d1 DATETIME, t1 NCHAR(10))

DECLARE @.String NVARCHAR(30)
SET @.String = '21/12/2007 14:15:15'

SET DATEFORMAT dmy

INSERT INTO tblDateTime
SELECT SUBSTRING(@.String, 1, 10), REVERSE(SUBSTRING(REVERSE(@.String), 0, 9))

Is this anything like what you're doing?

|||

Hi,

The problem is solved now.

richbrownesq, you were right. My problem was only one (is enough...) record wrong.

Thanks a lot for ALL!!!!

Problems converting Access UNION query to SQL

I've got a Union query that works in Access but I'm unable to get it to work
querying a SQL Server. Access was using linked tables so the data was
really coming from SQL anyway. I want to make this query work within
Reporting Services, but I'm receiving the following error message:
ADO error: ORDER BY items must appear in the select list if the statement
contains a UNION operator.
I was able to take the two Select statements from Access and make good SQL
queries with them, but I'm unable to UNION them together eventhough it works
fine in Access. Here's my two queries I'm trying to Union:
SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
(dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
= 0) AND (dbo.SOP30200.DOCID = 'inv')
ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
UNION
SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturns
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
dbo.viewSalesperson.SLPRSNID
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
'2006-03-01 00:00:00', 102)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
(dbo.SOP30200.DOCID = 'returns')order by of the first part of the union should be the problem.
Colin wrote:
>I've got a Union query that works in Access but I'm unable to get it to wor
k
>querying a SQL Server. Access was using linked tables so the data was
>really coming from SQL anyway. I want to make this query work within
>Reporting Services, but I'm receiving the following error message:
>ADO error: ORDER BY items must appear in the select list if the statement
>contains a UNION operator.
>I was able to take the two Select statements from Access and make good SQL
>queries with them, but I'm unable to UNION them together eventhough it work
s
>fine in Access. Here's my two queries I'm trying to Union:
>SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
> (dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
>= 0) AND (dbo.SOP30200.DOCID = 'inv')
>ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
>UNION
>SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturn
s
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
> dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
>dbo.viewSalesperson.SLPRSNID
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
> '2006-03-01 00:00:00', 102)) AND
>(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
>(dbo.SOP30200.DOCID = 'returns')
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Any suggestions on what I need to do to fix it?
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:5cf4869c57190@.uwe...
> order by of the first part of the union should be the problem.
> Colin wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Syntax is:
SELECT ... FROM ...
UNION (ALL)
SELECT ... FROM ...
ORDER BY...
(I also wonder why a TOP 100 PERCENT. That should return everything and
requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
RLF
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>|||Thanks for the help and feedback. I've got it working now. Is there a
reason why Visual Studio or any other design interface doesn't support
Design mode for UNION of two queries?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uWwHb5uQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> Syntax is:
> SELECT ... FROM ...
> UNION (ALL)
> SELECT ... FROM ...
> ORDER BY...
> (I also wonder why a TOP 100 PERCENT. That should return everything and
> requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
> RLF
> "Colin" <legendsfan@.nospam.nospam> wrote in message
> news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
>|||A few notes that may help you (and others):
1. TOP 100 Percent ... ORDER BY was a workaround/hack/creative piece of SQL
to try to get the optimizer to sort values at a point in the query plan.
Unfortunately, it didn't really work in all cases, and it actually isn't
being honored at all in SQL 2005. So, please consider removing this from
your code in the future.
2. As you've seen from the other posts, ORDER BY should be applied to the
end of the complete statement to affect the presentation order of the
results returned to the client (and not each block of the UNION).
3. The column binding rules for Jet Red (Access's engine) never really
conformed to the ANSI standards. Given the installed base, it's not likely
to be changed. So, just be aware that the queries you may have in your
Access application are sometimes interpreted in slightly different ways.
Best of luck,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>