1

I am unable to make data type of a column as DATE while creating view, default data type is varchar2. How can I change it to DATE column.

Below is the column definition:

TO_CHAR(DATE'1970-01-01' + ( 1465484488329 / 86400000 ), 'YYYY/MM/DD HH24:MI:SS') AS StartTime

Output will be

2016/06/09 15:01:28

but it is in Varchar2 format as default. Please suggest how to make it as Date column type.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
ankitpandey
  • 339
  • 1
  • 7
  • 23

1 Answers1

1

to_char returns a varchar. Just omit it, and you should have a date:

DATE'1970-01-01' + ( 1465484488329 / 86400000 )
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Its omitting Hour, Minute and second in result set, i want to retain that data as well, – ankitpandey Jun 12 '16 at 20:14
  • 1
    @ankitpandey - no it isn't. Your client is displaying the date in a particular format, e.g DD-MON-YYYY. You wanted a date, and you have a date, including the time. If you want to display the date as text in a particular format then use `to_char()`, but you generally want to do that at the last possible moment. Most clients also let you define a specific format; or you can rely on NLS settings - but you don't know what someone else running the query will see. – Alex Poole Jun 12 '16 at 20:16
  • @AlexPoole - You mean, you tried the query without `TO_CHAR` and it is displaying you result in **YYYY/MM/DD HH24:MI:SS** format ? I queried it in SQL Developer and Connecting it via Putty, at both places i am getting only DATE without hour, minute and second, is this setting at DB level or client level – ankitpandey Jun 12 '16 at 20:25
  • 1
    @ankitpandey - client level. To show that do `alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'` and query your view. Note that affects how *all* dates are shown, and is session-specific. Your view should return the DATE data type, not a string, and let whoever queries it format it the way they need. – Alex Poole Jun 12 '16 at 20:34
  • @AlexPoole - I am not sure if alter session will change things for other users, i have checked other properties and below they are: `NLS_DATE_FORMAT DD-MON-RR, NLS_TIME_FORMAT HH.MI.SSXFF AM, NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM, NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR, NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR` Can i use any of these ? I mean converting it to timestamp and still Data type be Date ? – ankitpandey Jun 12 '16 at 20:35
  • @ankitpandey - no, they are for other data types, and are also session-specific. And timestamp is a completely separate data type to date. You can't control how other users see a date - unless you format it as a string, which you almost certainly don't want to do. A date has no intrinsic format, it's always up to the client how they display it. Anyway, you're going far from the topic of the question - and hijacking Mureinik's answer (sorry). – Alex Poole Jun 12 '16 at 20:39
  • @AlexPoole - You are right, i did alter session and now i can view complete Date and Time and this view is specific to my user. Thanks a lot for all explanation. Thanks Mureinik for solution. – ankitpandey Jun 12 '16 at 20:53