Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

date & time formatting

in Sql Server 2K8 - is there a slick method of displaying date and time so that the output would be something like:
04/05/2012 9:15 am
with one command?
and if so how about options to use 12 or 24 hour clock?

thanks ...
0
dgrafx
Asked:
dgrafx
1 Solution
 
wshark83Commented:
here's a useful link which converts dates to various formats:

http://www.sqlusa.com/bestpractices/datetimeconversion/

basically you need to use the convert(datetime,<datefield>) functions
0
 
wdosanjosCommented:
There is no out of the box CONVERT option that formats the date as mm/dd/yyyy hh:mm AM (or PM) or mm/dd/yyyy hh:mm (24h), you'll need to use the available formats and do some string manipulation.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
micropc1Commented:
Right, like wdosanjos said you'll need to do something like.. (not tested)

CONVERT(varchar(50), GETDATE(), 101) + ' ' + RIGHT(CONVERT(varchar(50), GETDATE(), 100), LEN(CONVERT(varchar(50), GETDATE(), 100)) - 12)

This may also help.. http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
dgrafxAuthor Commented:
isn't there a new time format ??? like convert(time,getdate()) ?
0
 
wdosanjosCommented:
time is a data type not a format, but could do something like this:
-- mm/dd/yyyy hh:mmAM
select convert(varchar(20), getdate(), 101) + ' ' + convert(varchar(20), convert(time, getdate()), 100)

-- mm/dd/yyyy hh:mm
select convert(varchar(20), getdate(), 101) + ' ' + left(convert(varchar(20), convert(time, getdate(), 121)), 5)

/* Output

-----------------------------------------
04/05/2012 11:18AM


--------------------------
04/05/2012 11:18

*/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now