Solved

SyBase SQL DataTime Format

Posted on 2014-03-08
6
294 Views
Last Modified: 2014-03-15
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.
0
Comment
Question by:triphen
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39915388
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.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39915390
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.
0
 

Author Comment

by:triphen
ID: 39916743
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.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 500 total points
ID: 39917739
Seems like there's no easy way to do that.  Here's the best I can come up with:

convert(varchar(20),timeord,1)+' '+
case when datepart(hh,timeord)=0 then '12:'+convert(varchar(2),datepart(mi,timeord))+':'+convert(varchar(2),datepart(ss,timeord))+'AM'
when datepart(hh,timeord)>12 then convert(varchar(2),datepart(hh,timeord)-12)+':'+convert(varchar(2),datepart(mi,timeord))+':'+convert(varchar(2),datepart(ss,timeord))+'PM'
else convert(varchar(2),datepart(hh,timeord))+':'+convert(varchar(2),datepart(mi,timeord))+':'+convert(varchar(2),datepart(ss,timeord))+'AM'
end

Open in new window

0
 

Accepted Solution

by:
triphen earned 0 total points
ID: 39918183
I actually got it with this.

select convert(varchar(20),timeord,1) || right(convert(varchar(20),timeord,9),9) || right(convert(varchar(30), timeord, 9),2) from dba.posdetail

Thank you so much!
0
 

Author Closing Comment

by:triphen
ID: 39931102
Less code for the same result.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 61
mysql joining from the same table 6 42
SQL Query Syntax Join 4 39
How to use three values with DATEDIFF 3 20
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question