Solved

date & time formatting

Posted on 2012-04-05
6
192 Views
Last Modified: 2012-04-05
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
Comment
Question by:dgrafx
6 Comments
 
LVL 6

Expert Comment

by:wshark83
Comment Utility
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
 
LVL 22

Expert Comment

by:plusone3055
Comment Utility
0
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 7

Expert Comment

by:micropc1
Comment Utility
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
 
LVL 24

Author Comment

by:dgrafx
Comment Utility
isn't there a new time format ??? like convert(time,getdate()) ?
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
Comment Utility
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now