• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

Datetime HH:mm - zero missing

Hello
I would like to select hour and minutes from datetime. I would like to convert @MyDate so it becomes '09:05'
When I do following I get '9:5'  How do I convert to get zero as well?

DECLARE @MyDate datetime='2015-03-16 09:05:57.000'

select
CAST(DATEPART(HOUR, @MyDate) as varchar(50))+':'+CAST(DATEPART(MINUTE,@MyDate) as varchar(50))
0
johnson1
Asked:
johnson1
  • 2
1 Solution
 
Lokesh B RDeveloperCommented:
Hi,

Use the following format

select Right(Convert(varchar(16), @MyDate, 121),5)

Open in new window


Example here

select Right(Convert(varchar(16), CAST('2015-03-16 09:02:37.723' AS datetime), 121),5)

select Right(Convert(varchar(16), CAST('2015-03-16 10:15:37.723' AS datetime), 121),5)

Open in new window

0
 
PortletPaulfreelancerCommented:
select convert(varchar(5), @MyDate ,108)

for other date/time styles see: http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html

---
btw: if you have SQL 2012+

select format(@MyDate, 'hh:mm')
0
 
johnson1Author Commented:
Thank you:-)
0
 
PortletPaulfreelancerCommented:
STYLE 108 is provides just the TIME (it ignores date)

so just take the first 5 chars of the time

DECLARE @MyDate datetime='2015-03-16 09:05:57.000'

SELECT CONVERT(varchar(5), @MyDate ,108)

Result:
09:05
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

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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