CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
user13632549
user13632549

Reputation: 3

oracle query to bring the date like 2021-06-23 00:00:00.000, 2021-06-23 23:59:59.000 for current date

oracle query to bring the output like this 2021-06-23 00:00:00.000,

date between 2021-06-23 00:00:00.000 and 2021-06-23 23:59:59.000 current date with 0 hours 0 minutes and current date with 23 hours 59 minutes

Upvotes: 0

Views: 1798

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

When looking for a range of dates (or timestamps) it's simpler and safer to use two conditions to define the start and end, rather than using between. So for a date column you could do:

where your_column >= date '2021-06-23'
and your_column < date '2021-06-24'

and for a timestamp column you could do:

where your_column >= timestamp '2021-06-23 00:00:00'
and your_column < timestamp '2021-06-24 00:00:00'

though both would work for either data type.

To base it on today - and you would need to choose between sysdate and current_date - you could use trunc() to get midnight today, and add a day to that to get the upper bound:

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

or

where your_column >= trunc(sysdate)
and your_column < trunc(sysdate) + interval '1' day

optionally explicitly casting those to timestamps.

db<>fiddle with dates.
db<>fiddle with timestamps.

The reason I think it's safer is that you could do:

where your_column between timestamp '2021-06-23 00:00:00'
                      and timestamp '2021-06-23 23:59:59'

but if it turned out that your column was a timestamp and has values like 2021-06-23 23:59:59.123 then those would be excluded - because the .123 fractional seconds puts the value after the upper bound, when you would might have been expecting it to be included in that day's range.

db<.fiddle

To display the query results in a particular format you can either set your session NLS settings, as I did in the db<>fiddle demos, which your client can then use to display the native date/timestamp datatype values - though some clients have their own settings, and an application will have its own handling; or to get a string value you can explicitly convert:

select to_char(your_column, 'YYYY-MM-DD HH24:MI:SS.FF3') as result
from ...

Note that the FF format model is only valid for timestamps.

Upvotes: 1

Related Questions