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-07-14 : 17:44:41

[code]

I want a result from the based on the fromdat and todate which should be a valid dates..

select
StudentId,
Student_DT_MNTH ,
Student_DT_DT,
Student_DT_YR
From Student_info where
CAST(CAST(Student_DT_YR AS VARCHAR) + RIGHT('0' + CAST(Student_DT_MNTH AS VARCHAR), 2) + RIGHT('0' + CAST(Student_DT_DT AS VARCHAR), 2) AS
DATETIME) Between @FROMDATE And @TODATE

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-14 : 17:53:23

[code]-- Peso 1
select StudentId,
Student_DT_MNTH,
Student_DT_DT,
Student_DT_YR
From Student_info
where dateadd(month, 12 * Student_DT_YR - 22801 + Student_DT_MNTH, Student_DT_DT - 1) Between @FROMDATE And @TODATE

-- Peso 2
select StudentId,
Student_DT_MNTH,
Student_DT_DT,
Student_DT_YR
From Student_info
where STR(Student_DT_YR, 4) + STR(Student_DT_MNTH, 2) + STR(Student_DT_DT, 2) Between convert(varchar, @FROMDATE, 112) And convert(varchar, @TODATE, 112)

-- Peso 3
SELECT StudentId,
Student_DT_MNTH,
Student_DT_DT,
Student_DT_YR
FROM (
select StudentId,
Student_DT_MNTH,
Student_DT_DT,
Student_DT_YR,
REPLACE(STR(Student_DT_YR, 4) + STR(Student_DT_MNTH, 2) + STR(Student_DT_DT, 2), ' ', '0') AS theDate
From Student_info
) AS d
where theDate Between convert(varchar, @FROMDATE, 112) And convert(varchar, @TODATE, 112)
and isdate(thedate) = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-07-14 : 18:15:34

Thanks Peso for the query....

Thanks Peso for the query..I got the output with the query.. i need to get the results of all data if NULLS are being passed for fromdate and todate which invalid dates data also..

Thanks for you help in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-14 : 18:18:46

[code]SELECT StudentId,
Student_DT_MNTH,
Student_DT_DT,
Student_DT_YR
FROM (
select StudentId,
Student_DT_MNTH,
Student_DT_DT,
Student_DT_YR,
REPLACE(STR(Student_DT_YR, 4) + STR(Student_DT_MNTH, 2) + STR(Student_DT_DT, 2), ' ', '0') AS theDate
From Student_info
) AS d
where theDate Between convert(varchar, COALESCE(@FROMDATE, '17530101'), 112) And convert(varchar, COALESCE(@TODATE, '99991231'), 112)
or isdate(thedate) = 0[/code]

N 56°04'39.26"
E 12°55'05.63"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-07-14 : 21:34:30

Thanks peso for the query !

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 20:18:51

Heh... the next thing to do is to hit the designer of that table straight in the mouth with a pork chop launched from point blank range using a 200# draw "Wrist Rocket". ;-)

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources