Help on conversion

We have a database where all of the times have been stored as integers equal to the number of seconds from midnight. For several reports I need to convert these back into Times.

Example

INT TimeValue
27000 7:30 A.M.
59400 4:30 P.M.

I've looked at the way the vendor is doing this for some reports and it just looks goofy and way too complicated.

Step 1 determine the hour: 27000/3600 = 7 (INT)
Step 2 determine the minutes: 27000/60 = 450 (INT)
Step 3 determine the remainder or actual minutes 450 - (7*60) = 30
Step 4 Contatanate results from steps 1 and 3 together 7 + ':' + 30

There has to be a better way!

Thanks,

BrentIf I were you, I would create a function that would do the math, and then call it when you needed the result.|||Yes, create a function, but create a simpler one. You ought to be able to use the DateAdd function, which can take seconds as a parameter. You can add the seconds to any integer date (use date zero, which is something like 1/1/1900) and then you can display it as whatever time format you want.

Sorry if this is a little fuzzy. I don't have access to SQL syntax help at the moment.

blindman|||Thanks for the help, I was able to get this to work with the following code:

1. Converting time:

Convert(varchar(10),Dateadd("s",[Fieldname],0),8) As outputname
from tblname

2. Figuring out the elapsed time between two values:

Convert(varchar(10),DateAdd("s",Datediff("s",DateAdd("s",[Fieldname1],0),DateAdd("s",[Fieldname2,0)),0),8)
from [tblname]

Brent

Originally posted by blindman
Yes, create a function, but create a simpler one. You ought to be able to use the DateAdd function, which can take seconds as a parameter. You can add the seconds to any integer date (use date zero, which is something like 1/1/1900) and then you can display it as whatever time format you want.

Sorry if this is a little fuzzy. I don't have access to SQL syntax help at the moment.

blindman

Help needed! Need to force leading whitespace characters on SQL import! =\

I know that this almost never happens... but Im dealing with an AIX flatfile-database conversion that brings 80 tables into SQL. Im not allowed to touch this stuff or to massage how its brought over to SQL, as it deals with medical records...

What I need to do though, is on creating my own table imports, for my "account" fields to match with the existing SQL conversion table "account" fields, I have to match requirements...

Existing account numbers are a total of 6 charaters. Account numbers with less than six characters contain leading whitespace character equivalents to total the six character spaces for "account number"

When I import records, I need to force the same requirement and have a min and max length of characters for "account number" = 6 characters and any account number less than 6 characters must also have the necessary whitespace character equivalent added to it.

How would I do this? It needs to be automated, as this is a process that will run nightly and cannot have a human sitting on it every day, 7 days a week... I cannot accurately join unless I can meet this requirement and my hands are tied because I can't change the way the formatting is done on the imported tables =(

Any help would be greatly appreciated... I'm quite stuck

Heh, well, I would go and tell whomever you talked to that said you can't play with the data "because it's medical records" to go fly a kite. There is nothing special about medical records beyond HIPAA, and that says absolutely nothing about what format medical data must be stored in. Either there is another reason you aren't allowed to space-trim something as simple as the account number, or the person who said that actually believes it.

When you import your records, you can tell it to import left(space(6)+fieldname,6) and that'll left pad the field to 6 places. Or you can do the same thing on your joins:

SELECT *

FROM Table1

JOIN Table2 ON table1.field1=left(space(6)+table2.field1,6)

Of course, the joins will run extremely slow, but that's what you get when you take an originally crappy format, then try to force SQL Server to work with said crappy format -- Crappy performance, and crappy code.

|||

Motley wrote:

Heh, well, I would go and tell whomever you talked to that said you can't play with the data "because it's medical records" to go fly a kite.

Oh man, dont' temp me... I was working till midnight last night manually creating what would have been a flawlessly executing DTS package because of this... =\

Motley wrote:

Of course, the joins will run extremely slow, but that's what you get when you take an originally crappy format, then try to force SQL Server to work with said crappy format -- Crappy performance, and crappy code.

Lol, yes...

Thanks for the suggestion, I'm going to try to implement that right now. Appreciate your quick response!

|||

Hmm... it pushes everything six digits over... how do I prevent that?

Here's how my select reads:

SELECT Accession,left(space(6)+SUBSTRING(PatNo, 0,CHARINDEX('.', PatNo)),6)as AccountKey,RTRIM(SUBSTRING(PatNo,CHARINDEX('.', PatNo) + 1,LEN(PatNo)))as DependentKey, LastwordNum, LastName, FirstName, DOB, ExamDate, ExamCode, Exam, OrderMD, ReadMD, CPTCode, DiagDesc, ExamStatus, Department

When I run it, it shoves the number over... so a number that would read "123456" becomes blank or null =\

|||Motley meant to tell you to use RIGHT(), not LEFT().

SELECT Accession,RIGHT(SPACE(6)+SUBSTRING(PatNo, 0,CHARINDEX('.', PatNo)),6)as AccountKey,RTRIM(SUBSTRING(PatNo,CHARINDEX('.', PatNo) + 1,LEN(PatNo)))asDependentKey, LastwordNum, LastName, FirstName, DOB, ExamDate,ExamCode, Exam, OrderMD, ReadMD, CPTCode, DiagDesc, ExamStatus,Department

To make things easier for you, you might consider creating a UDF calledfnPadLeft which you can use each time you need that functionality. Here's a simple version, to which error checking code should be added:

CREATE FUNCTION dbo.fnPadLeft (@.myStringIN varchar(200),@.myPadCharacter char(1),@.myPadCount int)
RETURNS varchar(200) AS
BEGIN
DECLARE @.myStringOUT varchar(200)
SELECT @.myStringOUT = RIGHT(SPACE(@.myPadCount)+@.myStringIN,@.myPadCount)
RETURN @.myStringOUT
END

Usage example:

SELECT dbo.fnPadLeft('Terri',' ',10)

|||LOL, oops, my bad. Tmorton is right, I meant RIGHT(Space(6)+field,6) not LEFT(space(6)+field,6)