CODEWITHSUNDEEP
sql-server-2008
user1967253
user1967253

Reputation: 41

How to convert a 5 digit INT used as time and display it as HH:MM:SS in SQL

I am working on a data query and one of my fields has a time format of '33600' and is a data type (INT). I need to know how to convert the field so that it displays the correct time format of HH:MM:SS. The current query I used gives me the date/time as MM/DD/YYYY:00:00:00.

Ex: Convert(datetime,Cast(Ap.aptApptTime as varchar(6),3)as Appt_time

This produces the correct appointment date but gives 00:00:00 for the time. I need to display just the correct time based on a value like '33600'.

Any help on this would be greatly appreciated.

Upvotes: 3

Views: 5873

Answers (2)

Jim
Jim

Reputation: 2146

Assuming that 33600 is the number of seconds, you could also try

Cast(Cast((33600/86400.0) as smalldatetime) as time)

Where 86400.0 is the total number of seconds in a day. Don't forget the ".0" or SQL will treat it as an integer and the result will always be zero or midnight. You also can't cast directly from a number to a time field, so you have to use an intermediate smalldatetime field.

Upvotes: 0

MarkD
MarkD

Reputation: 5316

If 33600 is a count of milliseconds, this should work.

DECLARE @Var TIME = '00:00'
SELECT DATEADD(MS, 33600, @Var)

If seconds;

DECLARE @Var TIME = '00:00'
SELECT DATEADD(S, 33600, @Var)

Etc...

Upvotes: 2

Related Questions