?
Solved

SyBase SQL DataTime Format

Posted on 2014-03-08
6
Medium Priority
?
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

752 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