Conversion of mintues from midnight to regular time 1

[arodri]

Hello,

I have a stored procedure written that's pulling data from a linked server (firebird database).
It's pulling data on employees, and their clock-in time. Below is a sample of the data:

Site Loc Name Date TimeIn
2 TX LastN1, FirstN1 03/26/2011 1204
2 TX LastN2, firstN2 03/27/2011 825
2 TX LastN3, FirstN3 04/02/2011 946
2 TX LastN4, FirstN4 03/26/2011 940


I'm not sure how to approach the TimeIn column. Right now that clolumn is mintues from midnight. So for example, if we take the second record, their TimeIn is 825.

Divide that by 60 (825/60) and you get 13.75. Now it's SORT OF in 24 hour time. So 13.75 in 12-hour time would be 1:45, which is the format that I need this column in.

If you guys have any idea on how to do this, I would really appreciate it.

Thanks!

 

[markros]

In SQL Server 2008 try

declare @t table ([Site] int,  Loc char(2),      Name varchar(20),   [Date] date,     TimeIn int)
insert into @t 
values (
2,      'TX',   'LastN1, FirstN1',   '03/26/2011',      1204),
    
(2,      'TX',   'LastN2, firstN2',   '03/27/2011',      825),
(2,      'TX',   'LastN3, FirstN3',   '04/02/2011',      946),    
(2,      'TX',   'LastN4, FirstN4',   '03/26/2011',      940)    

select *, convert(varchar(10),cast(DATEADD(minute, TimeIn, '20110101') as time),100) from @t

PluralSight Learning Library

 

[PWise]

how about

dateadd(n,timein,date)

 

[arodri]

Thanks for the reponses!

PWise,

dateadd(n,timein,date) worked at converting what I had to a datetime value, with 24 time. I think from here it should be pretty simple - I just need to separate the time from that value, and convert that to 12-hour time.


Thanks so much for your help.

 

[gmmastros]

The DateAdd function returns a DateTime data type. You cannot remove parts of a DateTime, so you will need to convert this to a string. During the convert process, you can use on of the formats this displays time in 12 hour format, like this:

SubString(Convert(VarChar(20), DATEADD(minute, TimeIn, '20110101'), 100), 13, 7)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

right (convert(varchar,dateadd (n,TimeIn,0),100),7)

 

[arodri]

Thanks so much guys! Worked perfectly