? Query to return all records in last hour

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

General SQL Server Forums

New to SQL Server Programming

? Query to return all records in last hour

Author

Topic

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-17 : 11:04:37

Hi

I am looking to write a query that returns all records Inserted in the last hour.

The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?

I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 11:16:17

SELECT * FROM YourTable WHERE DATEDIFF(n,CAST(yourvarcharcolumn as datetime),GETDATE())<=60

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-17 : 12:44:27

Thank you. This seems perfectly reasonable yet does not return the correct results. I am doing this on a test server with data from 2007. I have set the system time to 2007/10/25 14:15.

If I query the table for that day the following records are returned all greater than 13:15 (14:15 - 60 mins)

Date Study_Time Fld1 Fld2

2007/10/25 13:36:23 aaaa bbbb
2007/10/25 13:37:14 aaaa cccc
2007/10/25 13:35:27 dddd eeee
2007/10/25 13:37:34 eeee ffff

When using the script below I would expect four rows to be returned.

select * from TableName where datediff(n,CAST(Study_Time as Datetime), getdate())<=60

The Study_Time field is a varchar(12).

I have just tested on a live system and unfortunately does not return correct results.

I am very appreciative of your assistance.

Many thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-17 : 12:48:47

SELECT * FROM YourTable WHERE DATEDIFF(minute,CAST(yourvarcharcolumn as datetime),GETDATE())<=60

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 13:00:56

quote:
Originally posted by CyberSquealer

Thank you. This seems perfectly reasonable yet does not return the correct results. I am doing this on a test server with data from 2007. I have set the system time to 2007/10/25 14:15.

If I query the table for that day the following records are returned all greater than 13:15 (14:15 - 60 mins)

Date Study_Time Fld1 Fld2

2007/10/25 13:36:23 aaaa bbbb
2007/10/25 13:37:14 aaaa cccc
2007/10/25 13:35:27 dddd eeee
2007/10/25 13:37:34 eeee ffff

When using the script below I would expect four rows to be returned.

select * from TableName where datediff(n,CAST(Study_Time as Datetime), getdate())<=60

The Study_Time field is a varchar(12).

I have just tested on a live system and unfortunately does not return correct results.

I am very appreciative of your assistance.

Many thanks

Are date & time in different columns? then you need to concatenate them and use it inside DATEDIFF
select * from TableName where datediff(n,CAST(Date + ' '+ Study_Time as Datetime), getdate())<=60 assuming both of them are varchar type.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-17 : 16:00:47

please please please don't tell me those are varchar columns...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 16:08:23

Doesn't matter.
Dates are stored in "the right order", yyyymmdd hhmmss anyway.

SELECT *
FROM Table1
WHERE Col >= REPLACE(CONVERT(VARCHAR(19), DATEADD(HOUR, -1, GETDATE()), 120), '-', '/')

E 12°55'05.25"
N 56°04'39.16"

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-18 : 10:04:13

H Guys

Thanks for all your input. Unfortunately these still do not work. The information you have given me is of great help and I'll continue trying to resolve. I'll let you know how I get on. Hope you find it as interesting as I do. I am checking that the values of Getdate() etc are correct by merely running "select getdate()" at the same time in another query. So the results should be coming back as there are records to be returned for the getdate() less 60 mins. Date field is seperate from "Study_Time" field.

Concatenating the Date and Study_Time fields is returning a string error.

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 10:09:01

what error are you getting?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 10:12:03

quote:
Originally posted by spirit1

please please please don't tell me those are varchar columns...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Now-a-days it is widely used to store formatted dates

Madhivanan

Failing to plan is Planning to fail

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-18 : 18:10:03

I started with sodeep's solution.

I changed the system date to 25/10/2007 and four records should be returned as their time values
are 13:36:23, 13:37:14, 13:35:27; 13:37:54.

This returned no records.

To test I ran the four queries. The results were

select getdate()

2007-04-27 14:00:28.060

select * from Table where date = '25/10/2007' AND study_time <= '14:00'

(4 records returned with study_times 13:36:23, 13:37:14, 13:35:27; 13:37:54.

select cast(study_time as datetime)

(although all records in table were returned I'm only show the relevant 4)

1900-01-01 13:36:23.000
1900-01-01 13:37:14.000
1900-01-01 13:35:27.000
1900-01-01 13:37:54.000

select datediff(minute,CAST(study_time as datetime),getdate()) FROM Study.

The results were (although all records in table were returned I'm only show the relevant 4)

56443702
56443701
56443703
56443701

So for sodeep's solution, the value 56443701 is always going to be greater than 60 so will not return any results.
(I guess).

I'll work with visakh16 next.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 18:16:39

Why why why have you split up date & time values?

E 12°55'05.25"
N 56°04'39.16"

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-18 : 18:26:49

Peso... I didn't.....this is inherited table not a new table I've deigned. Unfortunately as a critical system I cannot change this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 18:27:10

Maybe because he/she read Jeff's blog: http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-18 : 18:52:33

You are much closer visakh16
select * from TableName where datediff(n,CAST(Date + ' '+ Study_Time as Datetime), getdate())<=60

returns 21 rows including the 4 that I need. I'll break this down into component parts and test

The concatenation seems to look ok with the varchars now showing like correct datetime values.

CyberSquealer
Starting Member

11 Posts

Posted - 2008-04-21 : 05:04:55

Thank you Visakh16.

I can confirm that on production server this works a treat.

select * from TableName where datediff(n,CAST(Date + ' '+ Study_Time as Datetime), getdate())<=60

Having gone through a load of literature it would appear that it is good to use varchar datatype for dates and times as they can be manipulated in many more ways.

Thanks though to everyone for their input.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 05:17:45

It is almost never a good idea to use VARCHAR to store dates. This is of many reasons.
Loose that habit and ALWAYS USE PROPER DATATYPE.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources