sqlsap-asesybase-ase15

How to change the date format in SQL select script

In my Sybase DB I have a table called employee_leaves. So the select query statement is as shown below:

SELECT EmployeeCode,EmployeeName,ApplicationDate  FROM dbo.employee_leaves

Where I have challenge is that the ApplicationDate comes in this format: 16/04/2023 7:09:47.563

From the Select Query statement, I want each of the ApplicationDate to be formatted and displayed as:

2023-04-16 07:09:47.563

yyyy-MM-dd ....

I tried this but not working:

SELECT EmployeeCode,EmployeeName,format(ApplicationDate,'yyyy-MM-dd,hh:mm:ss') FROM dbo.employee_leaves

How do I achieve this from the select * FROM dbo.employee_leaves

Solution

  • Assuming the ApplicationDate column is defined with a datatype of datetime ...

    In Sybase ASE you want to look at the convert() function with a focus on the 3rd argument (aka the style setting).

    For OP's desired format this should work:

    -- style=140 requires ASE 16+
-- style=140 returns 6 digits after decimal => varchar(23) should drop the last 3 digits

SELECT EmployeeCode,
       EmployeeName,
       convert(varchar(23),ApplicationDate,140)
FROM dbo.employee_leaves

-- ASE 15.x does not have style=140 so we need to get creative
-- will (obviously) also work for ASE 16

SELECT EmployeeCode,
       EmployeeName,
             convert(varchar(10),ApplicationDate,23)     || " " ||
             convert(varchar(8) ,ApplicationDate,20)     || "." ||
       right(convert(varchar(12),ApplicationDate,20),3) 
FROM dbo.employee_leaves

    NOTES: