query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-17 : 15:45:00

[code]
If the value is
@todt = null,
@fromdt = null
then it's not pulling all the records..

Please correct the below query to pull all the records if dates parameter are as null..
If dates are passed all the dates related records should be pulled..

select * from student where
REPLACE(STR(STU_DT_YR, 4) + STR(STU_DT_MNTH, 2) + STR(STU_DT_DT, 2), ' ', '0')
Between convert(varchar, COALESCE(@fromdt, '17530101'), 112) And
convert(varchar, COALESCE(@todt, '99991231'), 112)

Thanks for your help in advance
[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 15:57:09

Treat dates as dates not strings - this assumes @fromdt and @todt are DATETIME: (and why not store the STU_DT values as a single datetime value as well?


select * 
from   student 
where  convert(datetime, REPLACE(STR(STU_DT_YR, 4) + '-' + STR(STU_DT_MNTH, 2) + '-' + STR(STU_DT_DT, 2), ' ', '0') )
Between COALESCE(@fromdt, '1753-01-01') And COALESCE(@todt, '9999-12-31')

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-17 : 16:07:23

Thanks for correction..The date was stored in 3 columns..month,day,year.

Still i not got the correct results when the record with date,month,year is null it's not pulling in the results..
what could be cause..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 16:11:14

Oh, so you want rows with NULL STU_DT to pull back no matter what the @from and @to dates are?

The you should coalesce those values with a something that will be between your @from @to dates. like:

select *
from student
where coalesce(convert(datetime, REPLACE(STR(STU_DT_YR, 4) + '-' + STR(STU_DT_MNTH, 2) + '-' + STR(STU_DT_DT, 2), ' ', '0') ), COALESCE(@fromdt, '1753-01-01'))
Between COALESCE(@fromdt, '1753-01-01') And COALESCE(@todt, '9999-12-31')

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-17 : 16:30:25

If it is Nulls for fromdt and todate then it should return all the records irrespective of dates...If there are dates then it should return the output based on the dates..

Hope i am clear..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-17 : 16:46:23

select *
from student
where (@fromdt is null and @todt isnull) or
(
coalesce(convert(datetime, REPLACE(STR(STU_DT_YR, 4) + '-' + STR(STU_DT_MNTH, 2) + '-' + STR(STU_DT_DT, 2), ' ', '0') ), COALESCE(@fromdt, '1753-01-01'))
Between COALESCE(@fromdt, '1753-01-01') And COALESCE(@todt, '9999-12-31')
)

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 16:50:59

Yep - the only complication could be if the user pass just one of the dates. ie: everthing after @from date or everything before @toDate ?

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-17 : 18:38:25

Thanks webfred for the query
But it is pulling all the records which includes nulls when the dates parameters are passed.

I need the results to be based on the dates..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-18 : 05:28:53

Maybe this:

select * 
from student 
where (@fromdt is null and @todt isnull) or
(
REPLACE(STR(STU_DT_YR, 4) + STR(STU_DT_MNTH, 2) + STR(STU_DT_DT, 2), ' ', '0')
Between @fromdt And @todt
)

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 10:14:09

quote:
Originally posted by sqlfresher2k7

Thanks webfred for the query
But it is pulling all the records which includes nulls when the dates parameters are passed.

I need the results to be based on the dates..

sqlfresher2k7,
I think by now you should understand the concepts of these solutions. Have you tried working this out for yourself ???

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-18 : 10:22:16

Thanks !
I have tried the solution and i got the results...

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-18 : 15:04:39

[code]

I got an error with record

STU_DT_YR = /6
STU_DT_MNTH =6/
STU_DT_DT = 66

Error converting data type varchar to float.
Can you correct the query to exclude this record...

[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 16:01:04

Garbage in Gargage out :)
If STU_DT was a datetime value then you wouldn't have this problem.

Either clean up the data and don't allow bad values in there. AND/OR post the query you are using now and we will add that logic to your current version...

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-18 : 20:29:30

I m using this query..

select *
from student
where (@fromdt is null and @todt isnull) or
(
REPLACE(STR(STU_DT_YR, 4) + STR(STU_DT_MNTH, 2) + STR(STU_DT_DT, 2), ' ', '0')
Between @fromdt And @todt
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 10:32:38

What datatypes are @fromdt and @todt? What datatypes are STU_DT_ columns ?

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-19 : 11:52:29

@fromdt and @todt? datetime

STU_DT_ are varchar..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 13:00:24

try this. If this doesn't work then post some sample data that is breaking...


declare @fromdt datetime, @todt datetime
select @fromdt = '2009-01-01', @todt = '2009-12-31'

declare @student table (STU_DT_YR varchar(4), STU_DT_MNTH varchar(2), STU_DT_DT varchar(2))
insert @student 
select '2009', '4', '10' union all
select '2009', '8','27' union all
select '/6', '6/', '66'

select *, right('0000' + STU_DT_YR, 4) + '-' + right('00' + STU_DT_MNTH, 2) + '-' + right('00' + STU_DT_DT, 2)
from @student
where (@fromdt is null and @todt is null) or
(
isDate(right('0000' + STU_DT_YR, 4) + '-' + right('00' + STU_DT_MNTH, 2) + '-' + right('00' + STU_DT_DT, 2)) = 1
AND
convert(datetime, right('0000' + STU_DT_YR, 4) + '-' + right('00' + STU_DT_MNTH, 2) + '-' + right('00' + STU_DT_DT, 2))
       Between @fromdt And @todt
)

output:
STU_DT_YR STU_DT_MNTH STU_DT_DT 
--------- ----------- --------- ----------
2009      4           10        2009-04-10
2009      8           27        2009-08-27

Be One with the Optimizer
TG

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-19 : 15:05:15

Thanks for the query..
It works..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-19 : 15:11:31

You're welcome.

Try to work towards storing data in appropriately typed columns. You can always efficiently break a datatime into year, month, day, etc components but it is hard (and inefficient) to work with dates when they are stored in separate varchar columns.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources