-1

for the below mysqlplus query script, how to get DATE & TIME in DD-MMM-YY HH.MM.SS format for one column "evt_eventdate," only

set echo off
SET SQLFORMAT CSV 
SET FEEDBACK OFF 
SET ERRORLOGGING ON 
set encoding UTF-8
Spool C:/Users/bhsadmin/Downloads/Daily Export/Daily SMC Events.csv
SELECT /*csv*/ 
    evt_eventdate,
    itemtype,
  
FROM
    all_events
WHERE
    all_eventdate BETWEEN trunc(sysdate - 1, 'DD-MON-YYYY HH24:MI:SS') AND trunc(sysdate, 'DD-MON-YYYY HH24:MI:SS')
    AND lan_language_id = 1 ORDER BY evt_event_id; 
Spool off
exit

for the above i get this result enter image description here

when i run the sqlscript to store all data in CSV file, for the column evt_eventdate i get only DD-MM-YY. instead i need to get the date and time in the format DD-MMM-YY HH.MM.SS to be stored in CSV.

I tried to add below, but did not work alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Below is the expected output

enter image description here

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
mfaizy
  • 13
  • 3
  • Date formatting functionality differs between different SQL flavors. What RDBMS you actually use? – PM 77-1 Dec 23 '22 at 15:34
  • Are you using MySQL, Postgresql or Oracle (sqlplus). – jarlh Dec 23 '22 at 15:35
  • It's clear that you are using Oracle, not MySQL or PostgreSQL. I have edited the tags above. Please tag your question with the brand of database you are actually using. It will help you, because it will attract the attention of readers on Stack Overflow who are best able to answer your question. – Bill Karwin Dec 23 '22 at 15:58
  • Please don’t link to images, add all relevant information directly to your question preferably as editable text l – NickW Dec 23 '22 at 16:51
  • What is evt_eventdate column's datatype? – Littlefoot Dec 23 '22 at 20:29

1 Answers1

0
SELECT /*csv*/ 
    to_char(evt_eventdate,'DD-MON-YYYY HH24:MI:SS')
...
OldProgrammer
  • 12,050
  • 4
  • 24
  • 45