sql show date time as dd/mm/yy hh:mm:ss without using CONVERT(VARCHAR(10)

Question

I am using Ingres Db which is old and cant do much, I need to show datetime in specific format as

dd-mm-yyyy hh:mm:ss

DB retunrn me it as

01.02.2018 14:43:53

it is NOT SQL server so I CANNOT use CONVERT(VARCHAR(10) Is there a way to convert it to format i need ?

Where do you want to show the information? In a particular application? — Pedro Martins, Feb 21 '18 at 00:56
@PedroMartins it will go directly to excel, but i cant modify it in excel — Andrey, Feb 21 '18 at 01:02
Have you seen this? https://communities.actian.com/s/article/Working-with-Ingresdate-Date-Fields It refers a few functions like date_part — Pedro Martins, Feb 21 '18 at 01:07
@PedroMartins I saw it, its not working properly insted of adres its return me some randome number — Andrey, Feb 21 '18 at 01:14
Have you tried altering your II_DATE_FORMAT setting? (Use "ingprenv" to see what it's set to). In recent versions of Ingres you can also alter this display format within your SQL session using "set date_format 'name';", see docs for available date format names. — G Jones, Mar 01 '18 at 12:47

score 0 · Accepted Answer · answered Feb 21 '18 at 01:24

to show it as dd-mm-yyyy hh:mm:ss you have to do next

SELECT Varchar(Date_part('day', date('now')))
       + '-'
       + Varchar(Date_part('month', date('now')))
       + '-'
       + Varchar(Date_part('year', date('now')))
       + ' '
       + Varchar(Date_part('hour', date('now')))
       + ':'
       + Varchar(Date_part('minute', date('now')))
       + ':'
       + Varchar(Date_part('second', date('now')));

score 0 · Answer 2 · answered Feb 21 '18 at 09:17

0

Use Replace select REPLACE('01.02.2018 14:43:53','.','-')

answered Feb 21 '18 at 09:17

vivek kumar

63
3

Please add some explenation to your answer. – André Kool Feb 21 '18 at 09:47
@vivek kumar , your solution will only work if date saved as string! Because you cannot use replace cannot work wit ingresdate format – Andrey Feb 21 '18 at 19:49

sql show date time as dd/mm/yy hh:mm:ss without using CONVERT(VARCHAR(10)

2 Answers2