February 29, 2012 at 12:56 pm
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
February 29, 2012 at 1:10 pm
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
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'
February 29, 2012 at 1:15 pm
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
February 29, 2012 at 1:18 pm
You can use datediff to skip the hours
SELECT ExamsId ,DateOfExam , PAPERNAME,
FROM TBLEXAM
WHERE PAPERID =2 AND datediff(dd,DateOfExam,GETDATE()) = 0
February 29, 2012 at 1:37 pm
piotrka (2/29/2012)
You can use datediff to skip the hoursSELECT 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
February 29, 2012 at 2:26 pm
Sorry Dave
The link doesn't open
February 29, 2012 at 2:48 pm
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
February 29, 2012 at 2:54 pm
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
February 29, 2012 at 3:22 pm
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)
;
March 1, 2012 at 12:48 am
thanks a lot for all guys for giving me a new idea
March 1, 2012 at 12:38 pm
Dave
Your example works a lot faster
Thanx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply