Converting Date-Time to Date Conundrum

I need to be able to convert a Date-Time Field to a Date Fied in my SQL
Tables.
Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
but because it converts the date value to a string I can say goodbye to
localization.... any ideas on how to convert the field but still allow
localization.Try this function...
CREATE FUNCTION [dbo].[fnRemoveTimeFromDateTime] (@.InputDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.OUTPUT AS SMALLDATETIME
SET @.OUTPUT = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.InputDate)))
RETURN @.OUTPUT
END
"SAcanuck" wrote:
> I need to be able to convert a Date-Time Field to a Date Fied in my SQL
> Tables.
> Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
> but because it converts the date value to a string I can say goodbye to
> localization.... any ideas on how to convert the field but still allow
> localization.

Converting Date-Time to Date

Hi: I am new to Reporting Services.
My issue is that I have a Date-Time Field which I want to use as a column heading for a crosstab. However since there are multiple date entries with varying times I get multiple column headings. Could anyone tell me how to convert this datetime field to a date field so that I only get one column per day.
ThanksThere are are number of VB.Net functions for manipulating dates which you
could use for this.
The simplest (but probably not most efficient) way would be to convert the
date to a string then back to a date: =CDate(CStr(Fields!DateTime.Value))
You might want to consider doing the conversion in your query instead.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:F6AFC614-180B-4C75-94B8-DB3149275D3D@.microsoft.com...
> Hi: I am new to Reporting Services.
> My issue is that I have a Date-Time Field which I want to use as a column
heading for a crosstab. However since there are multiple date entries with
varying times I get multiple column headings. Could anyone tell me how to
convert this datetime field to a date field so that I only get one column
per day.
> Thanks

converting datetime for comparison

I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?
declare @.Company varchar(50)
declare @.SerialNumber varchar(50)
declare @.ProductGroup varchar(10)
declare @.InstallationDate datetime
set @.Company = ''
set @.SerialNumber = ''
set @.ProductGroup = '9'
set @.InstallationDate = '21/03/2003 00:00:00'
select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@.InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @.Company + '%'
and p.SerialNumber like '%' + @.SerialNumber + '%'
and t.ProductTypeID like '%' + @.ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@.InstallationDate, 11), 103)
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" wrote ...

> Can somebody tell me what I am doing wrong in my code?
A guess only...but..

> declare @.InstallationDate datetime
^^ a datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'

> convert(char(11), left(@.InstallationDate, 11), 103)
char(11)
Perhaps the different data types is the problem?
Something else you could try which might help you achieve the same result
would be using the DATEDIFF function, if you are trying to find matches for
the same day, just use DATEDIFF, specify days, and look for where it equals
0 (ie, no days difference)..
Hope this helps..
Rob|||I have some best practices for datetime here: http://www.karaszi.com/SQLServer/in...
o_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike P" <mike.parr@.gmail.com> wrote in message news:e3zsKC2nGHA.3348@.TK2MSFTNGP03.phx.gbl.
.
>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
> declare @.Company varchar(50)
> declare @.SerialNumber varchar(50)
> declare @.ProductGroup varchar(10)
> declare @.InstallationDate datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
> p.InstallationDate,
> t.ProductTypeID, t.[Description], convert(datetime,
> left(p.InstallationDate, 11), 103),
> convert(datetime, left(@.InstallationDate, 11), 103)
> from Products p
> inner join companysite s
> on p.OwnedByCompanyID = s.CompanySiteKey
> inner join Competitors c
> on c.CompetitorID = p.ProducedByID
> inner join CompetitorMachines m
> on p.MachineID = m.CompetitorMachineID
> inner join ProductTypes t
> on t.ProductTypeID = m.ProductTypeID
> where s.[name] like '%' + @.Company + '%'
> and p.SerialNumber like '%' + @.SerialNumber + '%'
> and t.ProductTypeID like '%' + @.ProductGroup + '%'
> and convert(char(11), left(p.InstallationDate, 11), 103) =
> convert(char(11), left(@.InstallationDate, 11), 103)
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
Well, what is the problem? Do you get an error message, or is it not
returning any rows? Could you give us DDL, sample data, and desired results
as per http://www.aspfaq.com/5006 ?

Converting dates into floats

I have an odd database that has a date stored in a float field. The
dates originate in an external database, which is imported record-by-
record, with data cleanup and conversion, into SQL Server. We do many
of these sorts of conversions, but normally they are put into a
datetime field instead of a float.
Everything seems to work perfectly under VB. However, I am in the
process of converting from VB into SQL for a variety of reasons. This
floatdate is causing a problem, as it is always off by two days. Let
me give you an example...
On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
read in the date from the external database, which stores it as a
string: "20080605". Here is the code I use to convert it...
Function RealDate(Datestring) As Date
Dim Yr As String
Dim Mth As String
Dim Dy As String
On Error GoTo notadate
Yr = Left(Datestring, 4)
Mth = Right(Left(Datestring, 6), 2)
Dy = Right(Datestring, 2)
RealDate = Mth & "/" & Dy & "/" & Yr
Exit Function
notadate:
RealDate = 1 / 1 / 1900
End Function
So far so good. I then put that result, a VB Date, directly into the
float field in the database. If I then read that back out in VB and
cast it to a date (which is automatic if you want) I get back the same
value.
However, when I do this in SQL, I get a _slightly_ different date:
cast(price2 as datetime) as expiry
returns 2008-06-07 00:00:00.000
It's off by _two days_. At first I thought this was an epoch issue.
Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
Server uses 1/1/1900. Is this understanding correct? If so, how is it
that the resulting date in SQL is only off by two days, and not 70
years?
MauryMaury,
SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
somewhere along the line) that Visual Basic's zero day is 1899/12/30. (I
believe it was supposed to be 1899/12/31, so that makes two mistakes, one
for each day that your calculation is off.)
I am relying on memory since I cannot find the reference right now.
RLF
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:e8ffe61e-6b41-4b1c-b1ce-f25890d22ece@.d1g2000hsg.googlegroups.com...
>I have an odd database that has a date stored in a float field. The
> dates originate in an external database, which is imported record-by-
> record, with data cleanup and conversion, into SQL Server. We do many
> of these sorts of conversions, but normally they are put into a
> datetime field instead of a float.
> Everything seems to work perfectly under VB. However, I am in the
> process of converting from VB into SQL for a variety of reasons. This
> floatdate is causing a problem, as it is always off by two days. Let
> me give you an example...
> On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
> read in the date from the external database, which stores it as a
> string: "20080605". Here is the code I use to convert it...
> Function RealDate(Datestring) As Date
> Dim Yr As String
> Dim Mth As String
> Dim Dy As String
> On Error GoTo notadate
> Yr = Left(Datestring, 4)
> Mth = Right(Left(Datestring, 6), 2)
> Dy = Right(Datestring, 2)
> RealDate = Mth & "/" & Dy & "/" & Yr
> Exit Function
> notadate:
> RealDate = 1 / 1 / 1900
> End Function
> So far so good. I then put that result, a VB Date, directly into the
> float field in the database. If I then read that back out in VB and
> cast it to a date (which is automatic if you want) I get back the same
> value.
> However, when I do this in SQL, I get a _slightly_ different date:
> cast(price2 as datetime) as expiry
> returns 2008-06-07 00:00:00.000
> It's off by _two days_. At first I thought this was an epoch issue.
> Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
> Server uses 1/1/1900. Is this understanding correct? If so, how is it
> that the resulting date in SQL is only off by two days, and not 70
> years?
> Maury|||On Apr 29, 3:16=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
> somewhere along the line) that Visual Basic's zero day is 1899/12/30. =A0(=I
> believe it was supposed to be 1899/12/31, so that makes two mistakes, one
> for each day that your calculation is off.)
LOL! Ok, that DOES explain it. I'll just remember to -2 from now on.
Maury

converting date time field

Hi,

I'm trying to report on the time records are created , but not to include the date. So for a month long period, I want to know how many records have been created between 8am & 9am. I can group the records and display by hour, but as the database is a time date field, it displays for each date as well.

I think I probably need to create a formula that will strip out the date information, then I can group by hour and that will return what I need, but I have no idea how create such a formula...

Any ideas?

Thanks,

Matt.What is your Database?
Write a Stored Procedure having the query group by Time and use that sp to design the report

converting date field from Informix to SQL nvarchar

I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)

Any help is greatly appreciated!

JT

The goodness we share is rewarded twice!try to find the record that cause the failure:
select * From <your table name> where isdate(<date varchar column name>)=0

varchars in yyyy-mm-dd format usually converted to dates data type without problems...|||Thank you for the reply.

So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).

Run the select against the varchar column?|||You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0|||OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:

/*

Tuesday, April 13, 2004 13:20:17

User: sa

Server: NYCRPSTOR01

Database: Mysis

Application: MS SQLEM - Data Tools

*/

'findet' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.|||Originally posted by Brett Kaiser
You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0

Did you run this?

What did you get?

$1,000 bucks you've got non date data in that column|||That worked!!! Thank you for your help!!!|||What worked?

Did you find non date data?|||before you change the column type find what cause the conversion error!|||There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.

Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?

Again, thank you for your help! It is greatly appreciated!!!|||just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table

Converting data types

I've inherited a monster database that needs updating. It has a field called Birthdays that has been a text field and I want to convert it to datetime. The data entry people have been sloppy from time to time and some of the values generate errors when you try and change datatypes. Is there a way to change the datatype and have the oddball values left blank without bombing out the conversion process?

Thanksheh. how big is the database?

if it's not that big, then you can simply store them in a temp table, and move the values over one by one in a cursor style fashion. If it's absolutely enormous, I'd actually create another table just to store the data, and the same cursor fasion. Just make sure you put them in transactions so you can rollback accordingly based on any errors generated.

Since it's a one shot deal, you can even do it out of the database, and just manipulate all the values via code, then do the alter so it doesn't cause any problems.

Do you know specifically what the bad dates are? If so, via code, you can convert the dates based on how the errors were inputted, and update those records. :-\

Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>>I have a view with 3 integer fields: FailureCount, QuestionCount and
>>AuditCount. I want to get the following calculated result in a SELECT
>>statement:
>>SELECT FailureCount / (QuestionCount * AuditCount)
>>Problem is, this returns an integer result (as one would expect from 3
>>integers). I need the real number value, and CONVERT isn't working. I've
>>tried various syntaxes and I either get errors or no effect at all.
>>Any ideas how to properly do this?
>>Thanks,
>>Randall Arnold
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:

>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.
4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall Arnold
On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:

>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Converting an integer field into an Identity field

I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?

TIA,

Barkingdog

There is no TSQL statement to change a non-identity field to an identity field, even the designer will do strange things behind the scenes, like creating a new table copying the data to the new one, renaming the new and dropping the old table. SO you either do the same in your TSQL statements or use the gUI which does all the steps for you behind the scenes.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Converting Access cross-tab query to SQL Server

I have an Access cross-tab query that conditionally breaks up a field into multiple groups and averages the results in each group and I am having trouble doing this in SQL Server. The SQL Server query I have below works but does not find the averages by node A, B, RA, or RB.

Is there a better way to do this than what I have below?

How would I do this in SQL Server?

In the Access query I use:

right( [Serial No], iif( len([Serial No])-3>=0, len([Serial No])-3, len([Serial No]))

Table:

[Work Order No] [Serial No] [Parameter Name] [Type] [Min] [Max] [Value] [Pass/Fail]

M1000 001A "Test Name" "System1" 0 100 50 P

M1000 001B "Test Name" "System1" 0 100 40 P

M1000 002A "Test Name" "System1" 0 100 45 P

M1000 002B "Test Name" "System1" 0 100 70 P

M1000 002RA "Test Name" "System1" 0 100 30 P

M1000 002RB "Test Name" "System1" 0 100 20 P

M1001 001A "Test Name" "System1" 0 100 50 P

M1001 001B "Test Name" "System1" 0 100 30 P

The Query Output should be:

[Work Order No] [Node] [Min] [Max] [Avg Value]

M1000 A 0 100 47.5

M1000 B 0 100 55

M1000 RA 0 100 30

M1000 RB 0 100 20

M1001 A 0 100 50

M1001 B 0 100 30

Access Query:

TRANSFORM Avg([Value]) AS AvgOfValue

SELECT [Work Order No], [Min], [Max], RIGHT( [Serial No], iif(len([Serial No])-3>=0,

len([Serial No])-3, len([Serial No]))) AS [Node], max([DateTime]) as [MaxOfDateTime]

FROM [Inspection Header] INNER JOIN [Inspection Data]

ON [Inspection Header].[Work Order No] = [Inspection Data].[Work Order No]

AND [Inspection Header].[Serial No] = [Inspection Data].[Serial No]

WHERE [Parameter Name] = "Test Name" AND [Pass/Fail] = "P"

GROUP BY [Work Order No], [Min], [Max], RIGHT([Serial No],iif(len([Serial No])-3>=0,

len([Serial No])-3, len([Serial No]))), [Pass/Fail]

PIVOT [Parameter Name]

Incomplete SQL Server Query:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER OFF

GO

ALTER procedure [dbo].[dt_AvgTest]

as

begin

select b.[Work Order No], b.[TN], c.[PF], d.[Min], d.[Max]

from (((select a.[Work Order No]

, avg(case a.[Parameter Name] when "Test Name" then [Value] end) as [TN]

from dbo.[Inspection Data] as a group by a.[Work Order No]) b

inner join (select [Work Order No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No] ) c on c.[Work Order No] = b.[Work Order No] )

inner join (select [Work Order No], [Min], [Max]

from dbo.[Inspection Data] where [Parameter Name] = "Test Name" group by [Work Order No], [Min], [Max] ) d

on d.[Work Order No] = c.[Work Order No] )

where c.[PF] = 'P'

end

Sam:

Is your server running SQL Server 2000 or SQL Server 2005?

Dave

|||SQL Server 2005|||

-- -
-- 1. I am guessing based on the proposed output data that
-- the "Serial No" field must be broken apart to get the
-- [Node data]. This is a poor choice for data design.
-- This means that this field is not "atomic" and if
-- possible should be divided into two separate columns.
-- 2. I am assuming here that the "Parameter Name" column
-- contains quote characters as part of the target data.
-- 3. I am assuming here that the "Type" column contains
-- quote characters as part of the target data.
-- 4. I have chosen to display the "average value" field as
-- a numeric (9,1) column. It is unclear what the
-- precision of this field needs to be based on the
-- proposed output; it is likely that this display
-- column needs adjustment.
-- 5. The use of the 10-Level REPLACE function to remove
-- the numeric portion of the "Serial No" field to
-- obtain the "Node" portion of this field is a guess
-- that the "numeric" portion of the field is NOT a part
-- of the "Node". If this guess is not correct then
-- an alternate method of obtaining this field is
-- necessary.
-- 6. The "Min" and "Max" field are aggregated as a
-- precaution in case these fields should ever be
-- different for different line items. If these are
-- not aggregated an additional line will be generated
-- whenever these items might otherwise differ.
--
-- Questions:
-- 1. Can the "Serial No" field be broken down into
-- two separate fields?
-- 2. What are the rules for obtaining the "Node" data?
-- 3. What is the output specification for the "Value"
-- field.
-- -

select [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10) as [Node],
min ([Min]) as [Min],
max ([Max]) as [Max],
convert (numeric (9,1), avg (convert (numeric (9,1), [value])))
as [Value]
from [Inspection Data]
where [Pass/Fail] = 'P'
and [Parameter Name] = '"Test Name"'
group by [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10)
order by [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10)

-- -- Sample Output

-- Work Order No Node Min Max Value
-- - -- -
-- M1000 A 0 100 47.5
-- M1000 B 0 100 55.0
-- M1000 RA 0 100 30.0
-- M1000 RB 0 100 20.0
-- M1001 A 0 100 50.0
-- M1001 B 0 100 30.0

|||

This worked great!

You are right about breaking the Serial Number down into two separate fields.

Thank you very much.

Sam

Converting Access 2000 Query - IIf statement to SQL Server 2000 Vi

I have a field in the query where I count the tech_area field. I have an Iif
(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
My question is how do I translate that in SQL Server? I tried to write the
query using the new view window but I kept erroring when I put the IIf
statement in.
Any help would be greatly appreciated.
Thx,
CLM
You can use a Case expression instead. There is no IIF in
T-SQL or ANSI SQL. You can find more information and some
examples of using Case in SQL Server books online.
-Sue
On Mon, 21 Mar 2005 14:27:02 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I have a field in the query where I count the tech_area field. I have an Iif
>(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
>My question is how do I translate that in SQL Server? I tried to write the
>query using the new view window but I kept erroring when I put the IIf
>statement in.
>Any help would be greatly appreciated.
>Thx,
>CLM

Converting Access 2000 Query - IIf statement to SQL Server 2000 Vi

I have a field in the query where I count the tech_area field. I have an Ii
f
(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
My question is how do I translate that in SQL Server? I tried to write the
query using the new view window but I kept erroring when I put the IIf
statement in.
Any help would be greatly appreciated.
Thx,
CLMYou can use a Case expression instead. There is no IIF in
T-SQL or ANSI SQL. You can find more information and some
examples of using Case in SQL Server books online.
-Sue
On Mon, 21 Mar 2005 14:27:02 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I have a field in the query where I count the tech_area field. I have an I
if
>(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
>My question is how do I translate that in SQL Server? I tried to write the
>query using the new view window but I kept erroring when I put the IIf
>statement in.
>Any help would be greatly appreciated.
>Thx,
>CLMsqlsql

Converting a varchar to int

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.

But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9

A classic violation of first normal form:

http://en.wikipedia.org/wiki/First_..._ single_field
If at all possible, change your table to look like this:

Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

and then this simply becomes

SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)

That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.|||I can't change the actual table, but I can create a stored proc that
inserts it correctly into another table. I didn't even think to do
that (**duh**)! Thank you very much for your assistance!

Ed Murphy wrote:

Quote:

Originally Posted by

Patti wrote:
>

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

>
A classic violation of first normal form:
>
http://en.wikipedia.org/wiki/First_..._ single_field
>
If at all possible, change your table to look like this:
>
Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9
>

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

>
and then this simply becomes
>
SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)
>
That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)
>
SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'
>
Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.

|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Seems like some % are missing.

--
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|||This might work:
SELECT Loan#
FROM Table1
WHERE patindex('%[2,13,1]%',Codes) 0

Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9
>
I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/
>
Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.
>
But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.

|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>SELECT Loan#
>FROM Table1
>WHERE '-'+Codes+'-' like '-2-'
> OR '-'+Codes+'-' like '-13-'
> OR '-'+Codes+'-' like '-1-'

>
Seems like some % are missing.

Yes, of course you're right, should be

WHERE '-'+Codes+'-' like '%-2-%'
OR '-'+Codes+'-' like '%-13-%'
OR '-'+Codes+'-' like '%-1-%'

but the approach of "use a stored procedure to copy the data to a
better-normalized table" is probably better. (Oh, and that new
table should probably have an index on the Code column.)

converting a text field to number

I have a table with over a million rows and one of the fields contains
amounts of money in text format.
What is the most efficient way of converting this field to a number
format that I can sum on?

Regards,
CiarnHi Ciaran,

Before converting the type of the col, make sure that there are no
invalid values in that column. You can pull them out by

SELECT colName FROM tabName WHERE IsNumeric(colName) = 0

Alter your column type by

ALTER TABLE tableName ALTER COLUMN colName NUMERIC

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Nazeer Oasis (nazeerpp@.indiatimes.com) writes:
> Before converting the type of the col, make sure that there are no
> invalid values in that column. You can pull them out by
> SELECT colName FROM tabName WHERE IsNumeric(colName) = 0
> Alter your column type by
> ALTER TABLE tableName ALTER COLUMN colName NUMERIC

Unfortunately, this may still fail, since IsNumeric will approve of
values than converts to float or money, but not to numeric. Also, I
say that it's extremely bad practice to say numeric without specifying
scale and precision. You get some defaults, but these may not be what
you expect.

As for the original query, the easy way is:

SELECT SUM(convert(int, textcol)) FROM tbl
or SELECT SUM(convert(money, textcol)) FROM tbl

But this will of course fail if there are strings that does not convert.

If all data is integer, that is the text is undelimited and there are
no decimals, then it's pretty easy to test:

textcol NOT LIKE '%[0-9]%'

If the text can delimiters and decimals, it can become quite hairy
to filter.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> If all data is integer, that is the text is undelimited and there are
> no decimals, then it's pretty easy to test:
> textcol NOT LIKE '%[0-9]%'

This is wrong. I forgot a ^:

textcol NOT LIKE '%[^0-9]%'

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>

converting a string into a password varbinary field.

Hi,
I have a table that saves user information, name, password etc.
How do I convert the user entered password into a varbinary datatype?convert(varbinary(10), passwordfield) ?
Is this what you are looking for?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan McAuley" <ryan.mcauley@.sympatico.ca> wrote in message
news:TtZ0e.10317$JK1.876077@.news20.bellglobal.com...
> Hi,
> I have a table that saves user information, name, password etc.
> How do I convert the user entered password into a varbinary datatype?
>sqlsql