Times that aren't times

[sedgely]

I know you'll all tell me how bad this is but unfortunately i have inherited it and for the time being i cannot change it but here goes...

we have a system that populates access databases, these are then pulled into a SQl table, no probs so far.
within this table there is one field for holding a date and several fields with time values. The original systems stored these times as text so when they are pulled into SQl and converted to a date/time they have had a 'default' date added (1899-12-30). i now have to work with these fields and caluclate the differences between times, which doesn't seem like a problem apart from where the 2 times span midnight.
So the difference between 1899-12-30 22:00:00.000 and 22:30:00.000 would be 30 minutes, but the difference between 1899-12-30 23:45:00:.000 and 18-12-30 00:15:00.000 is 23 hours 30 and not 30 minutes which it MAY be depending on the date stored in the date field.
Can anyone think of a workaround for this?


Cheers, Craig
Si fractum non sit, noli id reficere

 

[vongrunt]

IIRC there is one "date" field (time fraction should be ignored if exists), and many "time" fields. For a single comparison, take any two of these time fields. Some may contain dates, some not. There are four (4) possibilities:

# Field1   Field2
-.-------.------
0 default default
1 default exact
2 exact   default
3 exact   exact

(default = date 1899-12-30, exact = date exactly specified). Is that correct?

 

[sedgely]

vongrunt
if the time fields contain a value i.e not NULL then it will always contain the default date and a specified time e.g. 1899-12-30 22:35:12.000
You are correct on everything else


Cheers, Craig
Si fractum non sit, noli id reficere

 

[yksvaan]

This example should give you idea how to proceed:

DECLARE @StopWatchSTART DATETIME, @StopWatchFINISH DATETIME
DECLARE @FinishTime DATETIME
SET @StopWatchSTART  = '1899-12-30' +  ' ' + '22:30:00'
SET @StopWatchFINISH = @StopWatchSTART - 0.4 -- TRY + 0.01
SELECT @StopWatchSTART , @StopWatchFINISH
SELECT @FinishTime = CASE WHEN @StopWatchFINISH < @StopWatchSTART
THEN @StopWatchSTART - DATEDIFF(d,0,@StopWatchSTART) - ( @StopWatchFINISH - DATEDIFF(d,0,@StopWatchFINISH) )
ELSE @StopWatchFINISH - DATEDIFF(d,0,@StopWatchFINISH) - ( @StopWatchSTART - DATEDIFF(d,0,@StopWatchSTART) )
END 
SELECT CONVERT(VARCHAR, @FinishTime, 114) AS FinishTime

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[yksvaan]

Have to mention that my suggestion gives you time difference between START and FINISH within 24 hours.
The line
SET @StopWatchSTART = '1899-12-30' + ' ' + '22:30:00'
might be confusing, it has nothing do to with you having that datepart in your case.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[sedgely]

Thanks yksvaan
Will 'play' around with that this afternoon and let you know what happens.


Cheers, Craig
Si fractum non sit, noli id reficere

 

[mcconnellj]

If you have a date for each start time and end time, you can use this to combine the date and time:

SELECT CAST(CAST('1899-12-30 22:35:12.000' AS DATETIME) + CAST('2004-12-17' AS DATETIME) AS DATETIME)

for each start and end date

Since 1899-12-30 represents a zero date, then adding the time + the date = datetime.

Then you can take the difference in the datetime.

Note: my SQL recognizes 1900-01-01 as a blank date, so using 1899-12-30 may need the addition of two days to get the correct datetime. However, if you are just trying to calculate the time, it does not matter as long as you use the same "blank" date on both ends.

Hopefully, I understood what you were asking.

 

[yksvaan]

SQL Server zero date is actually 1900-01-01 00:00:00,000 and not the vb legacy 1899-something.

sedgely, is your intention to subtract the timepart of finish_time from start_time (no matter what the daypart of the datetime content is) ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]