Querying Time (1 Viewer)

[rkrause]

I need to be able to query results from my table from yesterday at 7:01AM through today at 7:00AM. basically a full says worth of data. im not having any luck doing that. Any help would be great

 

[mdlueck]

What sort of column data type has the date/time? At least on MySQL I have many such queries. Some applications operate on UnixEpoc date/time tracking, so it was necessary to calculate the correct numbers in UnixEpoc format. Other web applications use a traditional datetime column datatype. so all that was necessary was to build the correct datetime strings to go from midnight one day to midnight the next day.

Assuming your date/time is in unencoded storage, query it along the lines of:

WHERE colname BETWEEN '2012-01-01 00:00:00' AND '2012-01-01 23:59:59'

Or if it is encoded, then you need to convert from the unencoded format to what ever encoding method is in use.

 

[rkrause]

ITs date/time, but when i end up using the date part as a parameter how would i do it then. Ultimately heres what i would want it to be

Where date Between @StartDate and @Enddate

my startdate would be 9-10-12 and enddate would be 9-11-12 but i would want data from yesterday at 7:01AM through today at 7:00AM

 

[mdlueck]

the date part as a parameter how would i do it then.

What specifically are you asking? Calculating an accurate date for "yesterday" taking into account "Leap Years" and "Second Tuesday's of the Week" situations?

I rely on the Open Object Rexx (ooRexx) programming language, with the built-in date/time classes. Much easier if I do not code date/time math code on my own! My MySQL queries I code in Bash, and Bash executes an ooRexx script when it has need to execute a "between" type query.

In my mind, you are looking for a trust worthy date/time class.

 

[rkrause]

In simple terms. forget the between. say i have a parameter 9/10/12(today) i would want data from yesterday at 7:01AM through today at 7:00AM

 

[mdlueck]

In simple terms. forget the between. say i have a parameter 9/10/12(today) i would want data from yesterday at 7:01AM through today at 7:00AM

"From yesterday at X through today at Y" IS a between type WHERE clause. Why would you want to forget about the BETWEEN keyword?

 

[rkrause]

meant forget it on the date only. just 1 date, but 2 times

 

[MSAccessRookie]

Perhaps a place to start is the GETDATE() function.

GETDATE() returns Today's Date.
GETDATE()-1 returns Yesterday's Date

Other Date Functions like DATEPART() could get you the rest of the way there.

 

[rkrause]

Yes i understand that. here is my where clause and it works if its run at 7AM everyday, but if its run at 10am then i only get data from 10-10. but i always want data from 7-7

where a.efnsent >= DATEADD(Day, -11,Getdate())

 

[pbaldy]

This worked in a brief test:

WHERE ReqDateTime between Cast(convert(varchar,getdate()-1, 101) As datetime) + '7:00:00' And Cast(convert(varchar,getdate(), 101) As datetime) + '6:59:59'

 

[mdlueck]

Yes i understand that. here is my where clause and it works if its run at 7AM everyday, but if its run at 10am then i only get data from 10-10. but i always want data from 7-7

wherea.efnsent>=DATEADD(Day,-11,Getdate())

Your syntax is only providing the date part of the date/time field, so I believe the time is considered to be a wildcard.

Best build more complete date/time strings. Try concatenating on the time portion statically.