|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
March 9th, 2005, 11:14 AM
|
|||
|
|||
Help with date functions - dateadd
Hey. Thought this would be an easy dateadd function but I cant get it to work!
Background: Using ASP.Net, VB.NET with SQl server 2000 DB. I have a table i need to query where I want to display all records between a user defined time and a pre defined time i.e Display all records between Now and 30 mins time OR Display all records between [user entered parameter i.e. 15:00] and 1 hour after user defined parameter. I want to have the user select in a drop down the time to query from and the amount of time after that. I hope I have explained that well enough! ALL records in the table are in the format 01/01/2005 12:00:00 i.e. there are different times throughout the day, and all of them are on the same day. On another point is there any way to change this is stored, I only want to bother with time, not dates, at the moment i convert before i display these values. anyway back to the problem, I have tried the following which I thoguht would work: Code: SELECT * FROM tblTimetable where sourcetime between getdate() and dateadd(hh,01,getdate()) Where sourcetime is a datetimefield with a typical value: 01/01/2005 12:00:00 obviously the getdate() function will show the date and time now today, so how can I get this to work? Thanks in advance!
|
|
#2
March 9th, 2005, 11:57 AM
|
||||
|
||||
|
To get all records where SourceTime is between Now and 30 minutes from Now just do this.
Code:
SELECT * FROM TblTimeTable WHERE SourceTime BETWEEN GetDate() AND DateAdd(n, 30, GetDate())
|
|
#3
March 9th, 2005, 01:02 PM
|
|||
|
|||
|
Hey,
Thanks for that, it works if I change all the dates stored in the database to todays date, however all my dates are in the past, so any ideas how I would be able to use the current time, and a date in the past say: 01/01/2005?? Ive tried a lot but cant seem to get anything that doesnt produce an error.
|
|
#4
March 9th, 2005, 02:02 PM
|
||||
|
||||
|
To get all records upto Now, just do this
Code:
SELECT * FROM TblTimeTable WHERE SourceTime <= GetDate()
|
|
#5
March 9th, 2005, 02:27 PM
|
|||
|
|||
|
hmm let me try and explain again what the problem is:
I have a table named timetable for storing the times when a bus shows up at the stop. The busses come at the same time each day so for each bus I am storing a bunch of times. I dont care about date its irrelevant to the table, however as sql is awkward I HAVE to specify a date for each time, so I chose 2005-01-01, so my table looks like this: BusTime BusNumber 2005-01-01 09:30 34 2005-01-01 10:30 54 2005-01-01 11:28 89 etc So I would like to be able to query the table to say display all the buses that run within the next hour, so obviously I want to use the getdate() function + 1hr function somewhere, obviously I arent interested in the date part of this function but only the time. I have tried along the lines of: Code:
SELECT sourcetime, busnumber FROM tblTimetable WHERE sourcetime between '2005-01-01'&(CONVERT(varchar,(GETDATE()),108) AND '2005-01-01'&DATEADD(h,01,GETDATE()) Any ideas?
|
|
#6
March 9th, 2005, 04:37 PM
|
||||
|
||||
|
Try something like this
Code:
SELECT *
FROM tblTimeTable
WHERE BusTime BETWEEN Cast('2005-01-01' + Cast(SUBSTRING(Cast(GetDate() As varchar), 14, 15) As DateTime) As DateTime) AND DateAdd(hh, 01, Cast('2005-01-01' + Cast(SUBSTRING(Cast(GetDate() As varchar), 14, 15) As DateTime) As DateTime))
|
|
#7
March 9th, 2005, 04:46 PM
|
|||
|
|||
|
Thats Spot on Memnoch!! You come across all EVIL!! But your good really!! Very good! that code is a bit of a mouthfull but it works!!!
Thank you!! 
|
|
#8
March 15th, 2005, 11:23 PM
|
||||
|
||||
|
Not to try and outdo the great and glorious Memnoch, but if you have the access to create and execute stored procedures on your database, you can make your job far easier by creating a sp like this:
Code:
CREATE PROCEDURE dbo.getBusSchedule (@hours as int)
AS
declare @now as DateTime
declare @referenceTime as DateTime
set @now = getDate()
set @referenceTime = dateadd(dd,datediff(dd,@now, cast('1/1/2005 12:00:00 am' as DateTime)),@now)
select *from tblTimeTable where BusTime between @referenceTime and dateadd(hh,@hours,@referenceTime)
GO
Then in your .net code, you can simply call the sp: "getBusSchedule 1" for one hour from now, or pass any number of hours you want: "getBusSchedule -3" would give you the buses for the previous 3 hours and so forth. Notice that I also give you one additional option for creating a specific time on the given date based on the current time. This may be easier to grasp than all of the fancy casting in Memnoch's post. (some copying and pasting will easily translate this into in-line sql if you can't use stored procedures -- but PLEASE look into using stored procedures instead.)
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Help with date functions - dateadd |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
