Converting fields
-
In a table I am editing the time field is stored as INT Eg 181533 for 15 mins 33 seconds past six.
I need to convert the field to a time format to enable me to query between 18:00:00 and 06:00:00 and cannot convert the field.
Any tips?
-
Why are you not storing this value in a datetime field? You wouldn't face this, and a few hundreds other problems like this one!!
-
Unfortunately I am unable to amend the database structure as the fields are specified by a system vendor.
-
DECLARE @Time AS CHAR(6)
SET @Time = '181533'
--this assumes that there are always 6 digits in the column
SELECT DATEADD(SS, CONVERT(INT, LEFT(@Time, 2)) * 3600 + CONVERT(INT, SUBSTRING(@Time, 3, 2)) * 60 + CONVERT(INT, RIGHT(@Time, 2)), 0)
Or you can always use 3 dateadds (one for hours, 1 for minutes and 1 for seconds).
-
Unless there is also a date column, there is no way to "query between 18:00:00 and 06:00:00'.... maybe between 06:00:00 and 18:00:00 but not the other way around.
So, is there also a date column?
--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) -
declare @tm int
set @tm = 181533
select dateadd(hh, @tm/10000, 0)+dateadd(n, @tm/100-(@tm/10000)*100, 0)+dateadd(ss, @tm-(@tm/100)*100, 0)
_____________
Code for TallyGenerator -
Hey Sergiy, I'm guessing you are using this method for performance. Is you're math version faster that my varchar version (no time to set up a test
).? -
...there is no way to "query between 18:00:00 and 06:00:00'... Oh yes you can
WHERE timefield >= 180000 OR timefield <= 60000
no need for conversion
But as you pointed out Jeff, need a date field for more precision but it might be that the results do not need to be date specific just data between 18:00:00 and 06:00:00 for any day
Far away is close at hand in the images of elsewhere.
Anon. -
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply