time

Question

Post reply

time

Mike Levan

SSChampion

Points: 14201
More actions
January 8, 2008 at 9:10 am

#62766

I have time field in milliseconds.
when its entered as 5.7 from the app it is saved as 2052000 in the DB. it shows that the value is div by 360000.
when am trying to convert millisec value back into original am getting the round number instead of decimal(5.7)
what conversion i need to use here?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Adrienne250 Right there with Babe Points: 737 More actions · Answer 1

You can cast either number as a float like:

select seconds = cast(2052000 as float) / 360000

or

select seconds = 2052000 / cast(360000 as float)

Mike Levan SSChampion Points: 14201 More actions · Answer 2

Adding to that..

How can i make sure that after decimal point there should be only 1 digit?

Adrienne250 Right there with Babe Points: 737 More actions · Answer 3

Just use the round function:

select seconds = round(2100045 / cast(360000 as float),1)

Mike Levan SSChampion Points: 14201 More actions · Answer 4

I dont need a round value, what i need is

I have to enforce a constraint where the user has to enter only 1 digit after decimal point, if its entered like 0.65 then it is an error. it shud be 0.6 only.

Adrienne250 Right there with Babe Points: 737 More actions · Answer 5

Sorry, will this work? (note the one less zero on the 360000)

select seconds = cast((2287045 / 36000) as float) / 10

Mike Levan SSChampion Points: 14201 More actions · Answer 6

the conversion part is working fine.

i just want to find fields where there is a value more than one digit after decimal point

Adrienne250 Right there with Babe Points: 737 More actions · Answer 7

Is this what you are looking for?

declare @temp as table (t int)

insert into @temp

select 234000 --.65

union all

select 2052000 -- 5.7

union all

select 1638000 -- 4.55

union all

select 1620000 -- 4.5

select *,seconds = t / cast(360000 as float)

from @temp

where t % cast(36000 as decimal) > 0

Mike Levan SSChampion Points: 14201 More actions · Answer 8

no its not working t sort out rows which has more than 1 digit after decimal point

Adrienne250 Right there with Babe Points: 737 More actions · Answer 9

Did you try my sample code? Does it work for you? What does your query look like?

Mike Levan SSChampion Points: 14201 More actions · Answer 10

your sample code is not working for me.

i just need to find out rows that has more than 1 digit after decimal point

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Adrienne (1/8/2008)
Is this what you are looking for?
declare @temp as table (t int)
insert into @temp
select 234000 --.65
union all
select 2052000 -- 5.7
union all
select 1638000 -- 4.55
union all
select 1620000 -- 4.5
select *,seconds = t / cast(360000 as float)
from @temp
where t % cast(36000 as decimal) > 0

Seriously... it's not working... try your own code and see the errors it generates...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

This will do it...

declare @temp table (t int)

insert into @temp

select 234000 --.65

union all

select 2052000 -- 5.7

union all

select 1638000 -- 4.55

union all

select 1620000 -- 4.5

select *, t/36000.0 ,t/36000

from @temp

WHERE t/36000<>t/36000.0

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Levan SSChampion Points: 14201 More actions · Answer 13

am not sure if am explaining it correctly..let me try again

I have time in millisec like 630000 in my DB for which it was entered as 1.75 from the app by the user.

but I want to restrict user to enter only 1.7 instead of1.75. if the user enters the value as 1.75 then i want to raise error.

That means 1st I have to find the value in DB (630000) and convert it into decimal which becomes 1.75, as it has more than 1 digit after decimal point I have to raise error, after converting if it comes to 1.7 then its a good value else an error.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Forcing a data entry mask (your "no more than 1-digit after decimal point") is a UI issue. The database server has no control over how data is entered. You should enforce your rule in whatever application you're using to let the users enter the data.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?