query help

i need to include in my query a filter for dates. i need to just return
records that have happened in the last six months. I do i do this. help
please.
i have a startdate and a enddate field.try date between startdate and dateadd(m,-6,startdate)
"Nat Johnson" wrote:
> i need to include in my query a filter for dates. i need to just return
> records that have happened in the last six months. I do i do this. help
> please.
> i have a startdate and a enddate field.

Query help

Hello Everyone,

Please can you help me with this?

I have an audit table, so there are many id's and modified dates... I am looking to get the last updated record for each ID. This is what I have so far... "which still gives duplicate ID's"

I have see many pages about distincta and max/min... I cannot make sense of it... HELP

1Select *2FROM TabelA3WhereExists (SELECT distinct max (id)as id ,max (ModifiedDate)as ModifiedDate4FROM TabelA)5Order by id

I tried to break it down, and still I get duplicate ID's

1Select *2FROM TabelA3Where idIN (SELECT distinct id4FROM TabelA)5Order by id

Try

SELECT *FROM TableAWHERECONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDEXISTS IN (selectCONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDas [key]from TableAgroup by ID)ORDER BY ID

|||

Thanks for your reply,

I get the following error

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'EXISTS'.

|||

SQL Server 2005:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate, row_number()OVER(partitionby idorderby ModifiedDateDESC)as RowNum

FROM TableA) t

WHERE t.RowNum=1

For SQL Server 2000, you can try:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate,(SELECTcount(*)FROM TABLEA aWHERE a.id=a1.idand a1.ModifiedDate<=a.ModifiedDate)as RowNum

FROM TableA a1) t

WHERE t.RowNum=1

|||

That's why your anAll-Star!....Shot for the help... it works very well.

Thanks B

sql

Query help

I am trying to find invalid dates in a column after loading from a text file
but the queries I have doesn't give me any result.
Because of the error, I have loaded column as a varchar rather than datetime
so that I can find the invalid dates. My query is:
Select col from dbo.mytable
where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
month
-----
Select col from dbo.mytable
where substr(col, 4, 2) NOT in
(01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) -- For Day
-----
Select col from dbo.mytable
where substr(col, 7, 2) NOT in (19,20) -- For year
-----
Typical dates in the text file are
01/12/1958
09/05/2007
04/23/1978
12/28/2003
01/01/1900
If I try to change the column data type from varchar to datetime I get an
error like 'can not convert to datetime, date is out of range'.
Thanks for any help.Well, I can think of a procedural approach.
Load the data as you have done and create a table variable with a datetime
column.
For each row, attempt an insert, or update to the table variable (put a
dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
somewhere else. Or on success, move the row to the final table, or choose
your own logic.
All that said, I was pretty sure DTS did this kind of thing for you.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>I am trying to find invalid dates in a column after loading from a text
>file
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than
> datetime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> For
> month
> -----
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> -- For Day
> -----
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> -----
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.|||The message from DTS is not what the data is. It is something like this (I
don't have the exact error):
Can not insert data Source column (Column number 70) data type string -
destination column (column_name) data type
DATE..................something like that which doesn't tell you what
the exact invalid date is. If the data is small, you can scroll it up and
down and find it but when it is 2.4 million rows, it is hard to find it
manually or when the text file is ~800 MB.........
"Jay" wrote:
> Well, I can think of a procedural approach.
> Load the data as you have done and create a table variable with a datetime
> column.
> For each row, attempt an insert, or update to the table variable (put a
> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that key
> somewhere else. Or on success, move the row to the final table, or choose
> your own logic.
> All that said, I was pretty sure DTS did this kind of thing for you.
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
> >I am trying to find invalid dates in a column after loading from a text
> >file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than
> > datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
> > For
> > month
> > -----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
> > -- For Day
> > -----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > -----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
>
>|||I've only used DTS when I had full control of the complete data stream, but
I though I remembered something about exceptions.
Still, if that isn't working for you and no one can help you with exception
filters in DTS, write the import filter yourself.
You could also try:
select *
from table
where datestr not LIKE '[0-1][0-9]/[[0-3][0-9]/[1-2][09][0-9][0-9]'
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:91B0F853-FD70-415D-A31E-17CE8EDC4E0E@.microsoft.com...
> The message from DTS is not what the data is. It is something like this (I
> don't have the exact error):
> Can not insert data Source column (Column number 70) data type string -
> destination column (column_name) data type
> DATE..................something like that which doesn't tell you what
> the exact invalid date is. If the data is small, you can scroll it up and
> down and find it but when it is 2.4 million rows, it is hard to find it
> manually or when the text file is ~800 MB.........
>
> "Jay" wrote:
>> Well, I can think of a procedural approach.
>> Load the data as you have done and create a table variable with a
>> datetime
>> column.
>> For each row, attempt an insert, or update to the table variable (put a
>> dummy row in 1st) and if the insert fails, or @.@.ROWCOUNT = 0, put that
>> key
>> somewhere else. Or on success, move the row to the final table, or choose
>> your own logic.
>> All that said, I was pretty sure DTS did this kind of thing for you.
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:BDE15221-20D3-46AE-B901-1965A8519493@.microsoft.com...
>> >I am trying to find invalid dates in a column after loading from a text
>> >file
>> > but the queries I have doesn't give me any result.
>> >
>> > Because of the error, I have loaded column as a varchar rather than
>> > datetime
>> > so that I can find the invalid dates. My query is:
>> >
>> > Select col from dbo.mytable
>> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) --
>> > For
>> > month
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 4, 2) NOT in
>> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
>> > -- For Day
>> > -----
>> > Select col from dbo.mytable
>> > where substr(col, 7, 2) NOT in (19,20) -- For year
>> > -----
>> >
>> > Typical dates in the text file are
>> >
>> > 01/12/1958
>> > 09/05/2007
>> > 04/23/1978
>> > 12/28/2003
>> > 01/01/1900
>> >
>> > If I try to change the column data type from varchar to datetime I get
>> > an
>> > error like 'can not convert to datetime, date is out of range'.
>> >
>> > Thanks for any help.
>>|||On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> I am trying to find invalid dates in a column after loading from a text f=ile
> but the queries I have doesn't give me any result.
> Because of the error, I have loaded column as a varchar rather than datet=ime
> so that I can find the invalid dates. My query is:
> Select col from dbo.mytable
> where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- F=or
> month
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 4, 2) NOT in
> (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,=25=AD,26,27,28,29,30,31) -- For Day
> ----=--=AD--
> Select col from dbo.mytable
> where substr(col, 7, 2) NOT in (19,20) -- For year
> ----=--=AD--
> Typical dates in the text file are
> 01/12/1958
> 09/05/2007
> 04/23/1978
> 12/28/2003
> 01/01/1900
> If I try to change the column data type from varchar to datetime I get an
> error like 'can not convert to datetime, date is out of range'.
> Thanks for any help.
Hi, there is an isdate function. Find the invalid dates and handle
them separately. HTH.|||That did it............Thanks..........
"SB" wrote:
> On Aug 28, 4:40 am, DXC <D...@.discussions.microsoft.com> wrote:
> > I am trying to find invalid dates in a column after loading from a text file
> > but the queries I have doesn't give me any result.
> >
> > Because of the error, I have loaded column as a varchar rather than datetime
> > so that I can find the invalid dates. My query is:
> >
> > Select col from dbo.mytable
> > where substr(col, 0, 2) NOT in (01,02,03,04,05,06,07,08,09,10,11,12) -- For
> > month
> > ----
> > Select col from dbo.mytable
> > where substr(col, 4, 2) NOT in
> > (01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25-,26,27,28,29,30,31) -- For Day
> > ----
> > Select col from dbo.mytable
> > where substr(col, 7, 2) NOT in (19,20) -- For year
> > ----
> >
> > Typical dates in the text file are
> >
> > 01/12/1958
> > 09/05/2007
> > 04/23/1978
> > 12/28/2003
> > 01/01/1900
> >
> > If I try to change the column data type from varchar to datetime I get an
> > error like 'can not convert to datetime, date is out of range'.
> >
> > Thanks for any help.
> Hi, there is an isdate function. Find the invalid dates and handle
> them separately. HTH.
>

Query Help

When I run this query it outputs the dates from the 14th of Nov to the 31st (well up to the last date in the db which atm is the 6th of dec) twice for each date because we have two order status values for each day.. Which is what I would like it to do..

However I would like to make it so if a particular row does not have a value <> 0 in any of the Orders fields that row is not displayed hence only displaying days/orderstatuses with actual orders and not days with 0's in all the COUNT(CASE WHEN discountoffercode = '##' THEN 1 END) Fields... I have tried so many things and I just cant figure out how to do this

Code: ( sql )

SELECT ReceivedDate, OrderStatus, COUNT(CASE WHEN discountoffercode = '88' THEN 1 END) AS OrdersCisco, SUM(CASE WHEN discountoffercode = '88' THEN ordertotal END) AS SalesCisco, COUNT(CASE WHEN discountoffercode = '89' THEN 1 END) AS OrdersTDS, SUM(CASE WHEN discountoffercode = '89' THEN ordertotal END) AS SalesTDS, COUNT(CASE WHEN discountoffercode = '90' THEN 1 END) AS OrdersBerbeeCDW, SUM(CASE WHEN discountoffercode = '90' THEN ordertotal END) AS SalesBerbeeCDW, COUNT(CASE WHEN discountoffercode = '91' THEN 1 END) AS OrdersQuadGfx, SUM(CASE WHEN discountoffercode = '91' THEN ordertotal END) AS SalesQuadGfx, COUNT(CASE WHEN discountoffercode = '92' THEN 1 END) AS OrdersATT, SUM(CASE WHEN discountoffercode = '92' THEN ordertotal END) AS SalesATT, COUNT(CASE WHEN discountoffercode = '93' THEN 1 END) AS OrdersGlobalCrossing, SUM(CASE WHEN discountoffercode = '93' THEN ordertotal END) AS SalesGlobalCrossing, COUNT(CASE WHEN discountoffercode = '94' THEN 1 END) AS OrdersPerformics, SUM(CASE WHEN discountoffercode = '94' THEN ordertotal END) AS SalesPerformics, COUNT(CASE WHEN discountoffercode = 'AA' THEN 1 END) AS OrdersOzburnHessey, SUM(CASE WHEN discountoffercode = 'AA' THEN ordertotal END) AS SalesOzburnHessey, COUNT(CASE WHEN discountoffercode = 'AB' THEN 1 END) AS OrdersFry, SUM(CASE WHEN discountoffercode = 'AB' THEN ordertotal END) AS SalesFry, COUNT(CASE WHEN discountoffercode = 'AC' THEN 1 END) AS OrdersEMC, SUM(CASE WHEN discountoffercode = 'AC' THEN ordertotal END) AS SalesEMC, COUNT(CASE WHEN discountoffercode = 'AD' THEN 1 END) AS OrdersGlasshouse, SUM(CASE WHEN discountoffercode = 'AD' THEN ordertotal END) AS SalesGlasshouse, COUNT(CASE WHEN discountoffercode = 'AE' THEN 1 END) AS OrdersSecureWorks, SUM(CASE WHEN discountoffercode = 'AE' THEN ordertotal END) AS SalesSecureWorks, COUNT(CASE WHEN discountoffercode = 'AF' THEN 1 END) AS OrdersPDS, SUM(CASE WHEN discountoffercode = 'AF' THEN ordertotal END) AS SalesPDS, COUNT(CASE WHEN discountoffercode = 'AG' THEN 1 END) AS OrdersMenashaPkg, SUM(CASE WHEN discountoffercode = 'AG' THEN ordertotal END) AS SalesMenashaPkg, COUNT(CASE WHEN discountoffercode = 'A2' THEN 1 END) AS OrdersBelmark, SUM(CASE WHEN discountoffercode = 'A2' THEN ordertotal END) AS SalesBelmark, COUNT(CASE WHEN discountoffercode = 'A3' THEN 1 END) AS OrdersPlasticIngeniuty, SUM(CASE WHEN discountoffercode = 'A3' THEN ordertotal END) AS SalesPlasticIngenuity, COUNT(CASE WHEN discountoffercode = 'A4' THEN 1 END) AS OrdersUFP, SUM(CASE WHEN discountoffercode = 'A4' THEN ordertotal END) AS SalesUFP, COUNT(CASE WHEN discountoffercode = 'A5' THEN 1 END) AS OrdersStyrene, SUM(CASE WHEN discountoffercode = 'A5' THEN ordertotal END) AS SalesStyreneFROM dbo.OrdersWHERE (ReceivedDate BETWEEN '20071114' AND '20071231')GROUP BY ReceivedDate, OrderStatus

Thanks for the edit :P

um anyone able to help me on this? It is kind of high priority and as i said i can't seem to figure it outsql

Query for 4 weeks average ..Need help

i have 3 tables, each with a date(it has daily dates) column(column name is same in all tables)
Each table has columns say "value1","value2", "value3"

i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week.

weeks count(value1) count(value2) count(value3 )
1/1/2005 101 88 221
1/8/2005 100 81 151
1/15/2005 87 96 301

Average calculations Here :
week 1 2 3 4
Count1: 101 = ( 99 + 105 + 110 + 87 )/4
100 = (105 + 110 + 87 + 98 )/4

Plz lemme know if u have any suggestions..

Do you really mean "SUM" where you say "COUNT"?|||Also, are you wanting your weeks to run Sunday to Saturday?|||

Sumit:

I put this together. It parameterized to allow for variation of (1) a "from date", (2) a "to date", and (3) the "beginning day of the week" [here I am assuming Sunday]. This routine uses a "small_iterator" table to flash through and summarize the records that occur during the date range. I am assuming that what you want are 28-day averages from the date named through the 28 days that follow. My "small_iterator" table consists of the integers 1-32768 and is intended as a utility table that we generally make avaible to all application databases. Our standards for this table stress the use of the NOLOCK optimizer hint for this table to avoid lock contention. This simple table is defined as:

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)

I hope the following is of use; I am not sure of all the requirements you have:

-- -
-- First, create a fake table with some fake data
-- -

set nocount on
create table ##xample
( xDate datetime not null,
value1 integer not null,
value2 integer not null,
value3 integer not null,

constraint pk_##xample primary key (xDate)
)

declare @.rootDate datetime
set @.rootDate = '11/19/2005' -- selecting a non-distinct date

declare @.iter integer
set @.iter = 0

while @.iter <= 250
begin

insert into ##xample
select dateadd (day, @.iter, @.rootDate),
1 + 60 * rand (),
1 + 40 * rand () + 40 * rand(),
1 + 50 * rand() + 50 * rand() + 50 * rand ()

set @.iter = @.iter + 1

end

--select * from ##xample -- To show the fake data if you want to see it

-- -
-- Establish some parameters to this report summary
--
-- In this example, we are going to assume that a week begins on Sunday
--
-- We are going to run this report from 1/1/2006 to the present; note
-- that since this uses an iterator table that the start date is set
-- to 12/31/2005 because dates are derived by using the iterator to
-- increment through the dates and the lowest iteration value is 1.
--
-- The @.baseWeekDate var is used to store the date on which the first
-- full week of the year begins minus one week (because of iterator table)
--
-- I am not sure about how the ranges are to run so maybe this helps,
-- maybe it doesn't
--
-- Notice that the "4-week" average rapidly shrinks for the data
-- at the end of the table; this is because we are taking a "4 week"
-- average with less than 28 days of data; you might want this handled
-- differently
-- -

declare @.fromDate datetime
declare @.toDate datetime
declare @.firstWeekDay integer
declare @.baseWeekDate datetime
declare @.maxIterator integer

set @.firstWeekDay = 1 -- Assume that Sunday is the beginning of the week
set @.fromDate = '12/31/5' -- The beginning of the year minus 1 day
set @.toDate = ( select max (xDate) from ##xample ) -- The highest date in the table
set @.maxIterator = 1 + datediff (day, @.fromDate, @.toDate) / 7 -- upper bound for iterator

-- -
-- Stuff is beginning to get more tricky here. I am looking for the
-- first Sunday the occurs at or after the "from date"; however, because
-- I am going to be using an iterator to bang throug the data, I must
-- back the that first Sunday date by a week.
-- -
select @.baseWeekDate = dateadd (day, -7 ,dateadd (day, iter, @.fromDate))
from small_iterator (nolock)
where iter <= 7
and datepart (dw, dateadd (day, iter, @.fromDate)) = @.firstWeekDay

-- Just used when I was debugging
/*
select @.fromDate as [@.fromDate],
@.toDate as [@.toDate],
@.firstWeekDay as [@.firstWeekDay],
@.baseWeekDate as [@.firstWeekDate],
@.maxIterator as [@.maxIterator]
*/

-- -
-- Heavy into it here:
--
-- This routine uses an iterator table to flash through all of the
-- starting week dates that occur between the from date and the to date
--
-- Compute the 4-week average for the data that begins with the listed
-- date and runs for the next 28 days
-- -
select convert (varchar (12), weekDate, 101) as [Week Date],
avgVal_1 as [Avg Val 1],
avgVal_2 as [Avg Val 2],
avgVal_3 as [Avg Val 3]
from ( select dateadd (day, 7*iter, @.baseWeekDate) as weekDate,
sum (value1) / 4 as avgVal_1,
sum (value2) / 4 as avgVal_2,
sum (value3) / 4 as avgVal_3
from small_iterator (nolock) -- don't want contention on an iterator
inner join ##xample
on xDate >= dateadd (day, 7*iter, @.baseWeekDate) -- bangs through all the sundays
and xDate < dateadd (day, 7*iter + 28, @.baseWeekDate) -- sets up a 4-week interval
where iter <= @.maxIterator
group by dateadd (day, 7*iter, @.baseWeekDate) -- Group the data by the week
) xx
order by weekDate

-- -
-- All done; let's drop the table and go home
-- -

go

drop table ##xample

|||Could you please post a sample schema, data and expected results?|||

I am so so thankful of u. i really wanted somthing of this type.

Now only problem is tat if the End Ref Date doesnt fall in the 4th week then the query will still give the average of 4 weeks, which is actually wrong.

i guess it should be like this

Last week --> no average

1 week b4 last week-->average of 2

2 weeks b4 last week --> avg of last 3 weeks

for other its as usual.

if u could reply me .it ll be really gr8..

Thanks & regards

Sumit

|||

Sumit:

In the comments I had:

--
-- Notice that the "4-week" average rapidly shrinks for the data
-- at the end of the table; this is because we are taking a "4 week"
-- average with less than 28 days of data; you might want this handled
-- differently

Is what you are seeking a solution to this problem that occurs over the last 28 days?

Dave

|||

Sorry for late reply..din see the Alert.

Actually ya u r rite..i was looking for average for last 28 days.

I had to make some reports on SQL Server2K Reporting Services.

The code which you sent, which included DDL n DML statements worked fine individually in Business Intelligence Studio but the dataset couldnt generate any particular fields. So i had to remove lot of things from the query, once i understood the login.it finally worked. Chart is coming fine.Thank u.

i have another question:

I have 3 fields say :

JOb Inactive Returned

ID1 2 3

ID2 5 1

ID3 2 6

ID4 1 5

ID5 5 4

ID6 2 6

ID7 1 5

i want data in such a way tat

Days_Count jobs_inactive Jobs_Returned

1 2 1

2 3 0

3 0 1

4 0 1

5 2 8 ( for 5 and Above days)

sql

Query Date Type - Somebody help me!p

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.where u have

Select * from Apro where data between Inicio and Final

should data be date ?? --> Date - Datetime
or u did that on purpose

Can u output the record source that is being executed ?

query by recent dates

How would I construct a SQL statement that would query the database and return the 5 most recent dates added into the database?

Would I use the TOP SQL keyword to select the 5 most recent entries?

How would I query for the most recent dates?

SELECT TOP 5 *
FROM dbo.tblWeblog
WHERE blogDate = ?

Thanks for any help!
-Dman100-Close! I'd use:SELECT TOP 5 *
FROM dbo.tblWeblog
ORDER BY blogDate DESC-PatP|||Thanks Pat!
-Dman100-

query between 2 dates error

Having this table with sales
select * from viewcomis2 where date between '05/27/2005' and '05/30/2005'
I got this weird result:
05/27/2005
04/28/2005 <-- this is not suppose to be here
05/28/2005
05/30/2005
I solved:
((substring(datee,1,2)) >= (substring('" & DTPicker1.Value & "',1,2)) and
(substring(datee,4,2)) >= (substring('" & DTPicker1.Value & "',4,2)) and
(substring(datee,7,4)) >= (substring('" & DTPicker1.Value & "',7,4))) AND
((substring(datee,1,2)) <= (substring('" & DTPicker2.Value & "',1,2)) and
(substring(datee,4,2)) <= (substring('" & DTPicker2.Value & "',4,2)) and
(substring(datee,7,4)) <= (substring('" & DTPicker2.Value & "',7,4)))
but still: 05-27-2005 >= 04-27-2005 ?
it was the only way to solve my problem but still have not answered my
original question on why some dates from others months appeared between
5.1.2005 and 5.30.2005
ah-> datee was from a view where convert(char(10),dbo.venta.SaleDate,103)
as datee
SaleDate is Datetime type, but I need to group them by day so I converted
to char(10) in order to query between dates. I know I did something wrong
somewhere but I have no clue on what or where. Any Ideas? ThanksHi
Did you try it this way:
select * from viewcomis2 where cast(date as datetime) between '05/27/2005'
and '05/30/2005'
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"humberto gonzalez" wrote:

> Having this table with sales
> select * from viewcomis2 where date between '05/27/2005' and '05/30/2005'
> I got this weird result:
> 05/27/2005
> 04/28/2005 <-- this is not suppose to be here
> 05/28/2005
> 05/30/2005
> I solved:
> ((substring(datee,1,2)) >= (substring('" & DTPicker1.Value & "',1,2)) and
> (substring(datee,4,2)) >= (substring('" & DTPicker1.Value & "',4,2)) and
> (substring(datee,7,4)) >= (substring('" & DTPicker1.Value & "',7,4))) AND
> ((substring(datee,1,2)) <= (substring('" & DTPicker2.Value & "',1,2)) and
> (substring(datee,4,2)) <= (substring('" & DTPicker2.Value & "',4,2)) and
> (substring(datee,7,4)) <= (substring('" & DTPicker2.Value & "',7,4)))
> but still: 05-27-2005 >= 04-27-2005 ?
> it was the only way to solve my problem but still have not answered my
> original question on why some dates from others months appeared between
> 5.1.2005 and 5.30.2005
> ah-> datee was from a view where convert(char(10),dbo.venta.SaleDate,103)
> as datee
> SaleDate is Datetime type, but I need to group them by day so I converted
> to char(10) in order to query between dates. I know I did something wrong
> somewhere but I have no clue on what or where. Any Ideas? Thanks
>