[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Format date in SQL

I have a datetime field (MS SQL 2008) and I would like to format it like this: 8/22/2014 10:25

I am trying to format it like: mm/dd/yyyy hh:ss (or mm-dd-yyyy hh:ss would be fine too.)

I tried using "convert" but I am not having any luck.

Could someone assist?
0
earwig75
Asked:
earwig75
1 Solution
 
Lee SavidgeCommented:
You might need 2 converts to get both parts. Couldn't see an obvious way.

declare @dt datetime
select @dt = GETDATE()

select CONVERT(nvarchar(8), @dt, 101) + ' ' + CONVERT(nvarchar(5), @dt, 108)

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
DECLARE @myDateTime DATETIME
SET @myDateTime = '8/22/2014 10:25'

--
-- Convert string
--
SELECT @myDateTime, CONVERT(VARCHAR, @myDateTime, 101) + ' ' + CONVERT(VARCHAR, @myDateTime, 114)

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
damn, 30 seconds to late!
0
 
Shaun KlineLead Software EngineerCommented:
If you want that format, you will need to use two separate CONVERT statements: (assuming your time format should have included minutes)

PRINT CONVERT(VARCHAR(10), GETDATE(), 110) + ' ' + CONVERT(VARCHAR(10), GETDATE(), 108)

Open in new window


In this format, hours uses a 24 hour clock.
0
 
PortletPaulCommented:
mm/dd/yyyy hh:ss

why would you not want minutes?

I'm intrigued

btw:  if you have SQL 2012 or later:

SELECT FORMAT(GETDATE(), 'MM-dd-yyyy HH:ss')

SELECT FORMAT(GETDATE(), 'MM-dd-yyyy HH:mm:ss') --<< more conventionally with minutes
SQL Server Date Styles (formats) using CONVERT()
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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