Handling Date columns in SQL

Question

I am trying to get some data from a SQL table. After reading the date the output looks like this

ID      BEGIN_DT                END_DT                   PX_LAST
----------------------------------------------------------------
13442   1900-01-01 00:00:00.000 1900-01-31 00:00:00.000      1

The data type shown in the output is DATE.

Can you let me know how to remove the timestamp 00:00:00.000 from the output?

Which DBMS? Date functions are very specific. You could try `cast(begin_dt as date)`, but again, that is not universal. — Isolated, Aug 24 '23 at 16:12
What database? Where do you see that output? If the type is `date` it shouldn't have any time component. Are you sure you aren't mistaking whatever your client shows for an actual value? At least in SQL Server `date` has no time component. The same with any database that differentiates between `date` and `datetime` or `timestamp`. SQLite has no types, much less a date type, and dates are stored using ISO8601 in columns with the text "facet" — Panagiotis Kanavos, Aug 24 '23 at 16:13
I am trying to see which DBMS I am using by the command SELECT * FROM information_schema.sql_implementation_info But that gives me the error `table or view does not exist` — Siddharth Somani, Aug 24 '23 at 16:24
In that case, check out the different ways that [db fiddle](https://dbfiddle.uk/) selects and displays the version based on DBMS. Try each method and compare your results. It's not all inclusive, but has many common systems. — Isolated, Aug 24 '23 at 16:31

Isolated · Answer 1 · 2023-08-24T17:50:07.530

0

In oracle, you can trunc() the timestamp or convert to char.

select 
  current_timestamp,
  trunc(current_timestamp) as trunc_dt, 
  to_char(current_timestamp, 'YYYY-MM-DD') as char_dt
from dual

CURRENT_TIMESTAMP	TRUNC_DT	CHAR_DT
24-AUG-23 18.49.48.795426 +01:00	24-AUG-23	2023-08-24

edited Aug 24 '23 at 17:50

answered Aug 24 '23 at 17:42

Isolated

5,169
1
6
18

Handling Date columns in SQL

1 Answers1