How to change the date format in SQL select script

Question

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

Answer 1

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:

• above queries tested/verified on an ASE 16.0 SP04 GA instance
• if the desired format is not available in the style chart you can typically build your own format with a combination of other convert(type,column,style), substring(), left(), right() and str_replace() calls
• if the ApplicationDate column is defined as varchar(N) (or char(N)) then you'll likely need to look at appending a series of substring() calls and literal strings to get the desired output format

Answer 2

This documentation can be referred for different outputs in case you need in future. Dont bother about the conversion mentioned in the document. We need only formatting.

The desired output may be achieved from the following code

SELECT EmployeeCode,EmployeeName,DATEFORMAT(ApplicationDate, 'YYYY-MM-DD HH:NN:SS.SSSSSS') AS ApplicationDate FROM employee_leaves

Allow us to advice you further in case this dint work as expected. The result has been obtained locally.