CODEWITHSUNDEEP
asp.netsqlsql-serverdatetime
Ravi
Ravi

Reputation: 31417

Retrieve data from database based on current date and time in SQL Server and ASP.NET

I was trying to get the information from my table Paper_Details based on current date and 15 min before the current time.

and this is my SELECT statement

objcmd.CommandText = "Select * from PAPER_DETAILS where ExamDate = Convert(date, getdate()) and StartTime = (Convert(time, getdate()) - 15)"

But, it is showing this error

Type date is not a defined system type.
Type time is not a defined system type

I'm using SQL Server 2005 and ASP.NET using VB.

I refer the above query from here

Thanks in advance !!

UPDATED

StartTime           |   ExamDate   
____________________|_____________________________
1/1/1900 4:20:00 PM | 7/27/2012 12:00:00 AM

The sample data of my table, which will help you to understand the scenario.

From, the above sample data, i wanted that, user can view the information respective to that row only on 7/27/2012 from 4:05:00 PM to 4:20:00 PM. I hope, everyone understood my scenario.

Upvotes: 0

Views: 12220

Answers (5)

Ravi
Ravi

Reputation: 31417

this is what, i finally got

objcmd.CommandText = "SELECT * FROM PAPER_DETAILS WHERE ((ExamDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AND ((DATEPART(minute, GETDATE()) >= DATEPART(minute, DATEADD(MINUTE, - 15, StartTime)))  AND (DATEPART(hh, GETDATE()) = DATEPART(hh, StartTime)) AND (DATEPART(minute, GETDATE()) <= DATEPART(minute, StartTime))    ))"

And, verified with some sample data, it works fine.!!!

Upvotes: 0

Steve
Steve

Reputation: 216343

objcmd.CommandText = "Select * from PAPER_DETAILS " + 
                     "where ExamDate=dateadd(dd,0, datediff(dd,0, getDate()) " + 
                     "and (getDate() >= DATEADD(n, -(DATEPART(minute, StartTime) " + 
                     "+ DATEPART(HOUR, StartTime) * 60) - 15, GETDATE())) and  " + 
                     "(GETDATE() <= DATEADD(n, (DATEPART(minute, StartTime) + " + 
                     "DATEPART(HOUR, StartTime) * 60), GETDATE()))"

I suppose that you ExamDate doesn't contains time information and thus you need a current date without time information to get an exact comparison.
Also, because your fields are already of DateTime kind, there is no need to CONVERT the results to DateTime

EDIT: Added a very complex test extracting StartTime hour and minutes and using them to build the limits for the checktime. I'm unable to test it, so please let me know if it works.

Upvotes: 1

Ram Singh
Ram Singh

Reputation: 6938

if you want to compare the date and time different-2 then try the following.

objcmd.CommandText = "Select * from PAPER_DETAILS " + 
                 "where CONVERT(varchar(20),ExamDate,101)=Convert(varchar(20),getdate(),101) " + 
                 "and CONVERT(varchar(20),StartTime,108)=CONVERT(varchar(20), dateadd(MINUTE,-15,getdate()),108)"

Upvotes: 0

Yaroslav
Yaroslav

Reputation: 6554

Use DATEADD to substract 15minutes from current time

Edited: take away the "CONVERT(DATE.." as you really dont need it and test again

SELECT *
  FROM PAPER_DETAILS
 WHERE ExamDate  = GETDATE()
   AND StartTime = DATEADD(MINUTE,-15,GETDATE())

Upvotes: 0

David Jiboye
David Jiboye

Reputation: 511

Use this

Select * from PAPER_DETAILS
where ExamDate >= DATEADD(MINUTE, -15, getdate()) and ExamDate <= getdate()

Upvotes: 0

Related Questions