Fetch records after 23:59:59

Question

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

Nitin Sontakke · Answer

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.

Smile · Answer

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

Sivakumar Koneti · Answer

https://stackoverflow.com/questions/176673/how-to-get-records-after-a-certain-time-using-sql-datetime-field/176686

Gajendra Jangid · Answer

hello hema, please have a look below code: SELECT convert ( varchar , getdate(), 14) ,* FROM CityMaster WHERE convert ( varchar , getdate(), 14) > '23:59:59:177' ;

Nitin Sontakke · Answer

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 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.

Smile · Answer

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

Amit Kumar · Answer

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) ))

Nitin Sontakke · Answer

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.