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

which Sybase RDBMS product (ASE? IQ? SQLAnywhere? Advantage?) and version are you using? — markp-fuso, May 26 '23 at 12:00
Did you try `select EmployeeCode,EmployeeName, convert(datetime, ApplicationDate,23) as ApplicationDate FROM dbo.employee_leaves` ? [Docs are here](https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/BABDBAHF.htm) — Kirs Sudh, May 26 '23 at 12:19
@KirsSudh - Yes. But this error: --------------- BMS -- Number (208) Severity (16) State (1) Server (MyDB) dbo.employee_leaves not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output — Bami, May 26 '23 at 12:26
`Msg 208` (object not found) means you have an invalid table reference (`dbo.employee_leaves`) in your query; this could be a case of running the query in the wrong database, or trying to access a table that's owned by some other user (ie, table is not owned by `dbo`) — markp-fuso, May 26 '23 at 12:37

markp-fuso · Accepted Answer · 2023-05-26T13:24:17.237

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

score 0 · Answer 2 · answered May 26 '23 at 12:38

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.

How to change the date format in SQL select script

2 Answers2