How to convert a "char(4)" datatype to "time" datatype

Question

I have a "char(4)" column whose values I need to change to a "time" datatype in 12 hour format.  For example, I have "1600" and I need to change that to "4:00pm".  I don't need to update the values in the database, only in a report.  Can I do this is t-sql?  I'm using SQL Server 2008 R2.

Thanks,

Jeff

---

Jeff Bandy

Answer 1

Try

cast(Left(col, 2) + ':' + Right(col, 2)  as time)

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

You may use followng code to get what you are looking for:

declare @tmInChar char(4)
set @tmInChar = '1600'
declare @tmInTime time
set @tmInTime = left(@tmInChar,2) + ':' + right(@tmInChar,2)

select convert(varchar(15),@tmInTime,100)

---

Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.

Answer 3

Simple question there is no simple answer.

DECLARE @t char(4) = '1600';
DECLARE @tm time = STUFF(@t,3,1,':');
SELECT @t, @tm
-- 1600	16:00:00.0000000

---

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 4

Here is another solution.

DECLARE 
@StrTime CHAR(4) = '1600',
@Time TIME
SELECT @Time = STUFF(@StrTime,3,1,':')
SELECT CONVERT(VARCHAR(20), @Time, 100)

Answer 5

Hi Kalaman,

You marked me unopposed but did you try my script? This will exactly return what was expected in the original request.

I request you to run once.

Regards,

Ashish. 

Answer 6

Your "solution" is exactly the same as Kalman's.  In addition, it is highly presumptive to mark your own posts as proposed answers - let others do that.  And if a moderator unproposes your post, re-proposing it again is not likely to win you many friends.

Answer 7

CELKO,

You didn't answer the question.  Looks like you're just trying to advertise your books...I see you're an author.  

So, I've reported your reply as "Spam/Advertising."

Next time try to give a more constructive answer.  I'm well aware we now have a Time datatype.

 

---

Jeff Bandy

Answer 8

Mr. Loski,

Thanks for your reply.  That gets me to 24-hour time, but is there a way to get to 12-hour format?  Need to convert the entire column.  I could use your suggestion and then convert to 12-hour but thought there may be a way to convert directly to 12 without doing 24 first?

Thanks,

Jeff

---

Jeff Bandy

Answer 9

Convert(varchar(100), cast(Left(col, 2) + ':' + Right(col, 2)  as time), 0)

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 10

Declare

@col as char(4)='1600'

select

left(cast(Left(@col,2)+':'+Right(@col,2)  as time),5)

Answer 11

CELKO,

You didn't answer the question.  Looks like you're just trying to advertise your books...I see you're an author.  

So, I've reported your reply as "Spam/Advertising."

Next time try to give a more constructive answer.  I'm well aware we now have a Time datatype.

 

---

Jeff Bandy

You haven't lived on this list if you haven't been chewed out either directly or indirectly by Joe Celko.  You might disagree with him, but I think that his point (which I can't find) is well worth thinking about.  If you structure your data using proper data types and let the applications worry about how the data is formatted, your job will be much easier.  You are paying the price for the database design decision that was made.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 12

Understood, however I'm not the person who designed the database nor do I have the mandate/authority to redesign it.

It should not be automatically assumed that a person is the one who "designed" the database just because they post a question here. 

You don't see his reply because I marked it as "Spam/Advertising" which removes it from the post.

I've marked your reply as the answer, and thanks for your input.

Jeff

 

---

Jeff Bandy

Answer 13

It should not be automatically assumed that a person is the one who "designed" the database just because they post a question here. 

If you notice, I deliberately used the Passive voice "You are paying the price for the database design decision that was made."  In English that means that I am trying to avoid the question as to who made the design decision. 

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 14

Celko,

Still yet, your reply doesn't even attempt to answer my question.

It seems you only reply to advertise your books.

So again, I will mark your reply as "Spam/Advertising" so it will not appear.

Lastly, I could not care less about your credentials.  A sincere and constructive answer is what would earn you respect.

---

Jeff Bandy

Answer 15

You are not doing anyone favors with this approach - including yourself.  By doing so, you (and the thread) now have comments that have no context.  Anyone reading this after the fact will find the thread difficult to follow.  In addition, you may not like his style but his posts are usually topical (and often even helpful) if you take the time to apply them to your situation.  We understand that you may not be able to correct the schema, but the lesson can still be learned and applied to future work. 

Posting in a public forum is a package deal - anyone can comment and you may not like the comments.  You are free to ignore them.  The ability to take criticism gracefully (regardless of how it is delivered) is a worthwhile skill to develop.

Answer 16

I was really hoping when I joined this forum that everyone would just be mature enough to offer a positive, constructive answer and leave out the demeaning comments.

It really does seem like that was too much to ask for.

---

Jeff Bandy