asked on

SyBase SQL DataTime Format

Sybase SQL Anywhere v10

I am using CONVERT(VARCHAR(20),t1.timeord, 100) and I get "Mar 07 2014 05:06PM"

I want shortened date and I need the seconds as well. 03/07/14 05:06:19PM

Thanks in advance.

wilcoxon

Assuming convert is the same between ASE and SQL Anywhere (100 is the same at least):

convert(varchar(20),t.timeord,1)+' '+convert(varchar(20),t.timeord,14)
gives you 03/07/14 17:06:19 (24-hour clock)

convert(varchar(20),t.timeord,1)+' '+convert(varchar(20),t.timeord,36)
gives you 03/07/14 05:06:19.000000PM (eg includes microseconds)

Oddly, I can't find any format that gives you exactly what you want.

wilcoxon

It looks like SQL Anywhere is a little different. Checking the ref docs, 36 isn't listed and 14 includes the microseconds. I'd suggest giving both my suggestions in my previous post a try and see if they work (and if they include the microseconds).

If it is important to remove microseconds, that can be accomplished by calling some other functions.

triphen

ASKER

select convert(varchar(20),timeord,1)+' '+convert(varchar(20),timeord,36) from dba.posdetail

Returns

03/07/14 2014-03-07 21:37:10.

select convert(varchar(20),timeord,1)+' '+convert(varchar(20),timeord,14) from dba.posdetail

Returns

03/07/14 21:37:10:079

Looks like both are 24 hour mode. I need 12 hour AM/PM with hour, minute, and seconds.

SOLUTION

wilcoxon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

triphen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

triphen

ASKER

Less code for the same result.