CODEWITHSUNDEEP
sqloracle-database
Venkatesh
Venkatesh

Reputation: 19

How to get start time and end time of a particular day in sql

I want to get the start date time and end date time of a day in sql. Eg : for today the Start date time is : Jan 8 2017 00:00:00 and end date time is Jan 8 2017 23:59:59

Upvotes: 1

Views: 4771

Answers (3)

rajquest
rajquest

Reputation: 711

--Start Of Day Datetime
 select dateadd(MINUTE, 0, dateadd(day, datediff(day, 0, getdate()), 0)) as StartDate

--End Of Day Datetime
select dateadd(MINUTE, 1439, dateadd(day, datediff(day, 0, getdate()), 0)) as EndDate

Upvotes: 0

MT0
MT0

Reputation: 168041

Using intervals (rather than magic numbers) so the code is self-documenting:

SELECT TRUNC( SYSDATE ) AS start_of_today,
       TRUNC( SYSDATE ) + INTERVAL '1' DAY - INTERVAL '1' SECOND AS end_of_today
FROM   DUAL;

Or, with timestamps:

SELECT CAST( TRUNC( SYSDATE ) AS TIMESTAMP(9) ) AS start_of_today,
       CAST( TRUNC( SYSDATE ) AS TIMESTAMP(9) )
         + INTERVAL '1' DAY - INTERVAL '0.000000001' SECOND(1,9) AS end_of_today
FROM   DUAL;

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269963

You can use:

select trunc(sysdate) as today_start,
       trunc(sysdate + 1) - 1 / (24*60*60) as today_end

For comparisons, though, I wouldn't use an "end" time for today. Use inequality and the next day:

where datetime >= trunc(sysdate) and datetime < trunc(sysdate + 1)

Upvotes: 4

Related Questions