3

I'm rather new to ASP.NET and SQL, so I'm having a tough time trying to figure out how to compare two time columns. I have a timestamped column and then a Now() column in an .mdb database. I need to have a gridview display records that are "Greater than or equal to 3 hours" from the timestamp. Any idea how I can accomplish this?

Jakub Konecki
  • 45,581
  • 7
  • 87
  • 126
Eric Hickman
  • 65
  • 1
  • 7
  • 2
    When to use datetime or timestamp is [HERE](http://stackoverflow.com/questions/5989539/when-to-use-datetime-or-timestamp) – Alaa Alweish Dec 13 '12 at 17:27

2 Answers2

0

The Transact-SQL timestamp data type is a binary data type with no time-related values.

So to answer your question: Is there a way to get DateTime value from timestamp type column?

The answer is: No

You need another column of datetime2 type and use > operator to for comparison. You might want to set default value of getutcdate() to set it when each row is inserted.

UPDATE:

Since the column is of datetime type and not timestamp type (there is a type in SQL Server called timestamp, hence the confusion) you can just do

WHERE [TimeCalled] <= DATEADD(hour, -3, GETDATE())

Make sure your server is running in the same timezone as your code. It may be safer to store all dates in UTC. In that case use GETUTCDATE instead on GETDATE

Jakub Konecki
  • 45,581
  • 7
  • 87
  • 126
  • Thank you for your reply. I am not sure I understand your reply, though. Let me better explain what I have going on. I have an .mdb file and I need to compare one column that got timestamped with a Now() function. I am entering records in the database and I use the Now() function to timestamp a "TimeCalled" column for when I enter that record. I then need to display in a gridview or listview all records that are 3 hours or older. This column is a "Date/Time" datatype. How would I go about writing an SQL statement for SELECTING those records that are 3 hours older or greater? – Eric Hickman Dec 13 '12 at 17:25
  • Thank you again for your quick reply! I will give this a shot right now! I wasn't sure how exactly to use the DATEADD function. Sorry again for the confusion! – Eric Hickman Dec 13 '12 at 17:32
  • Changed `>=` to `<=` as I thought that you want records within last 3 hours. – Jakub Konecki Dec 13 '12 at 17:36
  • When I try to execute that query in Visual Studio 2010, I am getting the following error: Error Source: Microsoft Jet Database Engine, Error Message: Undefined function "GETDATE" in expression. – Eric Hickman Dec 13 '12 at 17:46
  • Oh, you're using Access, sorry. You will need to pass it as a parameter. Just pass the value of `Now()` – Jakub Konecki Dec 13 '12 at 17:48
  • Ok, when doing that, I receive the following error: No value given for one or more required parameters. My query looks like this: SELECT ReferenceID, FirstName, LastName FROM customers WHERE (TimeCalled <= DATEADD([hour], - 3, NOW())) – Eric Hickman Dec 13 '12 at 17:52
  • No, you need to pass current datetime as a parameter to your SQL query. Read about parametrized queries. Or render the actual datetime in the query - `DATEADD([hour], - 3, '2012-12-13 13:23:22'))` – Jakub Konecki Dec 13 '12 at 17:56
  • When trying to use the actual time in the query I still get the error about no value being given. Not sure what I'm doing wrong. What does the "Hour" parameter do in that query? – Eric Hickman Dec 13 '12 at 18:17
  • Would using: SELECT ReferenceID, FirstName, LastName FROM customers WHERE (DATEDIFF([hour], TimeCalled, NOW()) >= 3) be a valid statement? By the way, I am typing this in the Query Builder part of Visual Studio. When I execute that query I still get an error saying that there is no vaule give for one or more require parameters. – Eric Hickman Dec 13 '12 at 20:50
  • Ok, after messing with it for a while, I finally got it to work using your statement, BUT I had to change "hour" to "'h'". It wasn't understanding what "hour" was. Thank you very much for your help and for excusing my ignorance! – Eric Hickman Dec 13 '12 at 22:01
0

Timestamps are generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you're using a DateTime Column and you want the result in TSQL try 

DATEDIFF(Hour, 'Your DateTime Column here', 'pass Now() here' )

try to execute this example in TSQL:

select DATEDIFF(Hour, '2012-11-10 00:00:59.900', '2012-11-10 05:01:00.100')
Alaa Alweish
  • 8,904
  • 16
  • 57
  • 84
  • When to use datetime or timestamp is [HERE](http://stackoverflow.com/questions/5989539/when-to-use-datetime-or-timestamp) – Alaa Alweish Dec 13 '12 at 17:25
  • Thank you for your reply, yes, I think I misused the term "timestamp". I was using it as a general term, not the actual timestamp function. I was using the Now() function when inserting a record. Therefore, it just catches the time that record was inserted, the problem is that I now need to check the table for records that are older than 3 hours. – Eric Hickman Dec 13 '12 at 17:30
  • If you're using a DateTime Column and you want the result in TSQL try `DATEDIFF(Hour, 'Your DateTime Column here', 'pass Now() here' )` – Alaa Alweish Dec 13 '12 at 18:02
  • I'm using visual studio 2010 and am working with asp.net. So I am trying to fill a gridview with records that are older than 3 hours. How would I do that using a SELECT statement? I'm not a very advanced programmer, so step by step instructions would be great, thank you! – Eric Hickman Dec 13 '12 at 20:03