Fetch records after 23:59:59

Smile

7y1.4kSQL

Select Pkey ,ArrivingTime,DepartureTime from table1 where Arrival between (select left(CONVERT(VARCHAR(8),getdate(),108),5)) and sELECT Convert(Varchar(5),DATEADD(MINUTE, 60, (SELECT DATEADD(MINUTE, 0,CONVERT(Time,GETDATE()))))) order by Arrival

Above is get the records from table on time based i.e from current time to next 60 minutes

This query works fine till 23:59:59 but unable to fetch records for next day i.e. 00:00 ,01:00 hrs

Any suggestions will be helpful

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke7y ago
With no date part stored and time part stored as varchar, it is not quite clear to me how you can possibly arrive at the correct solution.

At database level (and even at business layer) type integrity must be maintained.

Without date, how do you even know that it is a next day? If you could explain, please.

If related data is somewhere else, please share that as well.
0
Smile7y ago
The datatype for the Arriving time is varchar(10).the problem i am facing is when current time is around 23:30 hrs and the added value for current time is ex 60 minutes the time becomes 00:30.So i need to get the records which are between 23:30hrs and 00:30 hrs.
using the query which i poseted is fetching records till 23:59

My table
Pkey    ArrivingTime   DepartureTime
1           10:10           10:20
2           11:00              11:30
3              23:30           23:35
4              00:10           00:15
5              00:30           00:35

first i will be adding the minutes to current time and then apply in between condition.Hope i am clear

Thanks in Advance
0
Sivakumar Koneti7y ago
https://stackoverflow.com/questions/176673/how-to-get-records-after-a-certain-time-using-sql-datetime-field/176686
0
Gajendra Jangid7y ago
hello hema,

please have a look below code:
SELECT convert(varchar, getdate(), 14) ,* FROM CityMaster
WHERE convert(varchar, getdate(), 14) > '23:59:59:177';
0
Nitin Sontakke7y ago
What you need is actually a dateadd function, which it seems you already know about.

Here is an example:
declare @dateFrom datetime = '2019-01-15 02:00:00'
declare @dateTo datetime =  '2019-01-17 02:00:00'

declare @records table
(
  [ArrivalDate] datetime not null
)
declare @dateIndex datetime = @dateFrom
while @dateIndex <= @dateTo
  begin
    insert into @records select @dateIndex
    select @dateIndex = dateadd(N, 5, @dateIndex)
  end

declare @getDataFrom datetime = '2019-01-15 05:09:00'
declare @getDataTo datetime = dateadd(n, 30, @getDataFrom)

select *
from @records
where [ArrivalDate] between @getDataFrom and @getDataTo
However, the more important question is, what is the data type of your arrival time column? Does it store both date and time or just time? If it just stores time where is the date stored?
Please share some sample data (preferably in tabular format) to help you better.
0
Smile7y ago
My requirement is i need toget the records from the table on time basis i.e arrival time i.e between current time and x time i,e, current time + x(minutes).the above query works fine till end of the day i.e 23:59 but not working for next day i.e for 00:00 hrs
0
Amit Kumar7y ago
Hi,
You are saying write but for next day you have to add 1 day from your current day. You have add 60 minute for next day but you have use varchar(5) then is how hh:mm but not tell next day or current day.

Execute this query as it is. It work fine for me.
Select Pkey ,ArrivingTime,DepartureTime from table1 where Arrival between
(SELECT concat( CONVERT( VARCHAR(10), GETDATE(), 126) ,' ' +CONVERT(VARCHAR(18), GETDATE(), 108)  ))
AND
(SELECT concat( CONVERT( VARCHAR(10), dateadd(DD, 1, getdate()), 126) ,' ' +CONVERT(VARCHAR(18), GETDATE(), 108)  ))
0
Nitin Sontakke7y ago
Hello,

Much nicer if you could please let us know what you want to achieve. That is the basic requirement.

It is possible that the solution much simpler than you think.

Please share details.
0