Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SyBase SQL DataTime Format

Posted on 2014-03-08
6
Medium Priority
?
313 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 27

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 27

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 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