HERE I JUST WANT TO WRITE SELECT QUERY FOR A DATE BASE ON CURRENT DATE

Question

HERE I JUST WANT TO WRITE SELECT QUERY FOR A DATE BASE ON CURRENT DATE

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
February 29, 2012 at 12:56 pm

#253974

ExamsId DateOfExam PaperId PAPERNAME
1 2012-02-16 02:39:00.253 1 SOME
2 2012-02-18 02:40:16.530 2 ONE
3 2012-02-9 02:40:16.530 2 DD
4 2012-02-19 02:40:16.530 2 SHH
IMAGINE TODAY IS 19-02-2012
AND NOW I JUST WANT TO VIEW THE EXAM OF TODAY WHICH MEANS 19-02-2012
DEPEND ON PAPRAMETER @PAPER ID
I TRIED THIS WAY BUT IAM NOT GETTING CORRECT SOLUTION
@PAPERID INT
SELECT
ExamsId ,
DateOfExam ,
PAPERNAME,
FROM
TBLEXAM
WHERE
PAPERID =2
AND
DateOfExam= GETDATE()
I NEED OUTPUT LIKE THIS
ExamsId DateOfExam PAPERNAME
4 2012-02-19 02:40:16.530 SHH
I TRIED IN ANOTHER WAY ALSO
SELECT CONVERT(VARCHAR(10),DATEOF EXAM,112) FROM TABLE TBLEXAM WHERE PAPERID=2
CONVERT(VARCHAR(10),GET DATE(),112)
AND A CONDITION STATEMENT ALSO
IF(A=B)
SELECT
DATE OF EXAM
PAPERS
WHERE
PAPERID=2
BUT I DINT FIND THE SOLUTION
SO CAN ANY ONE PLZ TRY TO SOLVE MY PROBLEM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Request 1 - Lower ur Caps while posting the question. It is sore to the eyes while reading.

Request 2 - Though you have 75% succeeded in showing what you want and what your data is, to be 100% , read the following article and post your question honoring the etiquettes

Forum Etiquettes[/url]

Question to you:

If you know you want only a specific date's row, change your WHERE clause to

WHERE PaperID = 2 and ExamDate = '2012-02-19'

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

HomeWork ???

DateTime contains a date and a time , so although you think you are searching for a date (ie a whole 24hr period) you are not , you are search for an exact point in time (millisecond or so)

Try this link for info on date / times

http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog[/url]

piotrka SSCrazy Points: 2031 More actions · Answer 3

You can use datediff to skip the hours

SELECT ExamsId ,DateOfExam , PAPERNAME,

FROM TBLEXAM

WHERE PAPERID =2 AND datediff(dd,DateOfExam,GETDATE()) = 0

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

piotrka (2/29/2012)
You can use datediff to skip the hours
SELECT ExamsId ,DateOfExam , PAPERNAME,
FROM TBLEXAM
WHERE PAPERID =2 AND datediff(dd,DateOfExam,GETDATE()) = 0

Personally i wouldnt do this due to sargability issues , ie if you have an index , you wont be able to use it.

Take a look at the link i posted earlier for options around this

Clear Sky SQL
My Blog[/url]

piotrka SSCrazy Points: 2031 More actions · Answer 5

piotrka

SSCrazy

Points: 2031

February 29, 2012 at 2:26 pm

#1453377

Sorry Dave

The link doesn't open

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Link works fine here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

just in case here it is again....works fine for me

http://www.karaszi.com/SQLServer/info_datetime.asp

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Perhaps something like this:

SELECT

ExamsId ,

DateOfExam ,

PAPERNAME,

FROM

TBLEXAM

WHERE

PAPERID = 2

AND DateOfExam >= dateadd(dd, datediff(dd, 0, GETDATE()), 0)

AND DateOfExam < dateadd(dd, datediff(dd, 0, GETDATE()) + 1, 0)

;

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 9

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498

March 1, 2012 at 12:48 am

#1453466

thanks a lot for all guys for giving me a new idea

piotrka SSCrazy Points: 2031 More actions · Answer 10

piotrka

SSCrazy

Points: 2031

March 1, 2012 at 12:38 pm

#1453840

Dave

Your example works a lot faster

Thanx