June 7, 2008 at 11:50 pm
Unlike the MAC household I am working weekends... but this one deserves some further input.
Working with dates and times is straight-forward but what I am reading is the date/time data is VarChar. Please specify the format or you are going to get sunk..
So CAST(..) is fine but my 2c worth is it is going to get you into trouble - as you already noticed with the AM/PM problem.
Instead use CONVERT(DateTime, @DateVar, 106) for dates and CONVERT(DateTime, @DateVar, 108) for the time part; join them together and work with them as variables. You can choose whichever date/time format you are comfortable with but because you're converting from Var to Date be explicit.
Your Code:
SELECT [ARRIVAL_DATE] AS ArriveDate
,[ARRIVAL_TIME] AS ArriveTime
,LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2) AS AssessTime
,DATEDIFF (mi, [ARRIVAL_TIME], AssessTime) AS AssessWait
,LEFT([TIME_SEEN_FOR_TREATMENT],2) + ':' + RIGHT([TIME_SEEN_FOR_TREATMENT],2) AS StartTreatTime
,DATEDIFF (mi, AssessTime, StartTreatTime) AS TreatWait
,LEFT([ATTENDANCE_CONCLUSION_TIME],2) + ':' + RIGHT([ATTENDANCE_CONCLUSION_TIME],2) AS EndTreatTime
,DATEDIFF (mi, StartTreatTime, EndTreatTime) AS TreatTime
,[DEPARTURE_TIME]
Here I would definately specify the format of your time (and the same applies to any other vars you manipulate from Vat to date/time.
Example: LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2)
becomes
CONVERT(DateTime, LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2), 108) AS AssessTime
Then, on your Concatanate question:
[ARRIVAL_DATE] [ARRIVAL_TIME]
------------------------- ------------------------
2006-12-31 00:00:00.000 1900-01-01 00:26:00.000
2006-12-31 00:00:00.000 1900-01-01 00:28:00.000
Select CONVERT(DateTime, [ARRIVAL_DATE], 120) + CONVERT(DateTime, RIGHT(CONVERT(Char(19), [ARRIVAL_TIME], 120), 12), 114)
That seems round-about but it will work and is safe. I am assuming your date and time as displayed is machine default but the idea is to take no chances. The day you can specify as a date (it may already be a date field - you didn't say) but I would not take a chance with a straight addition of time. That's why the convert to a var, cut off the bit I want, and then back to a DateTime but using the format specifier.
Hope this helps.
June 9, 2008 at 2:01 am
Thanks Robert
This makes sense to me. 🙂
June 10, 2008 at 2:50 am
This returns a syntax error:
Select CONVERT(DateTime, [ARRIVAL_DATE], 120) + CONVERT(DateTime, RIGHT(CONVERT(Char(19), [ARRIVAL_TIME], 120), 12), 114)
but a small amendment and slight repositioning of the parentheses sorted that:
SELECT CONVERT(DateTime, [ARRIVAL_DATE], 120) + CONVERT(DateTime, RIGHT(CONVERT(Char(19), [ARRIVAL_TIME], 120), 114)) AS ArrDateTime
June 10, 2008 at 9:41 am
While this appears to be solved, I was just wondering why use CONVERT or CAST? I would think that if you already have DateTime data types, why not use the date arithmetic functions:
SELECT DATEADD(s,DATEDIFF(s,[ARRIVAL_TIME],'01/01/1900 00:00:00'),[ARRIVAL_DATE]) AS ARRIVAL_DT
This could be done with any combination of two DateTime fields where one has the time and the other has the date, and automatically solves any problem of where the time field is > 24 hrs. However, I am making an assumption that a time that would represent a duration of > 24 hrs and 48 hrs but < 72 hrs in a DateTime value of 1/3/1900 hh:mm:ss, etc., etc...
Am I at least thinking this through correctly?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2008 at 2:37 am
smunson (6/10/2008)
While this appears to be solved, I was just wondering why use CONVERT or CAST? I would think that if you already have DateTime data types, why not use the date arithmetic functions:
SELECT DATEADD(s,DATEDIFF(s,[ARRIVAL_TIME],'01/01/1900 00:00:00'),[ARRIVAL_DATE]) AS ARRIVAL_DT
This could be done with any combination of two DateTime fields where one has the time and the other has the date, and automatically solves any problem of where the time field is > 24 hrs. However, I am making an assumption that a time that would represent a duration of > 24 hrs and 48 hrs but < 72 hrs in a DateTime value of 1/3/1900 hh:mm:ss, etc., etc...
Am I at least thinking this through correctly?
Steve
(aka smunson)
:):):)
Steve
First, I would like to point out that I am not responsible for the failings of the data collection system!
The time data as originally received was not all DateTime: the arrival date and arrival time were two separate DateTime fields (I have no idea why); all the other times were simply 4 digit numbers to represent 24 hour clock times (except where the field was empty).
Consequently, for each record, where one time is before midnight but another time is after midnight the time interval appears to be negative.
I think you are suggesting that by concatenating the 24hr clock times with the arrival date the other timings would work in the way you describe above. Unfortunately I don't think that is so.
Regards
Tim
June 11, 2008 at 7:04 am
Please don't think that I'm making you responsible for a data collection faillure - most of us are put into positions of having to make do with less than ideal data. My difficulty was understanding clearly exactly what you had to work with, and my impression was that you had two DateTime fields to work with, as opposed to separate date & time fields.
Now that I've got that mess straightened out in at least MY head...
Provided that each 4 digit time field has an associated date field, it would seem reasonably straightforward to be able to use STRING functions to tie the two together into something that will implicitly convert within the date arithmetic functions I referred to previously... or am I still confused?
Here's the concept, which may or may not match the data you had to work with:
DECLARE @STARTDATE DateTime, @ENDDATE DateTime
DECLARE @SD char(10), @ED char(10), @ST char(4), @et char(4)
SET @SD = '06/01/2008'
SET @ED = '06/02/2008'
SET @ST = '1345'
SET @et = '0215'
SET @STARTDATE = DATEADD(s, 0, @SD + ' ' + LEFT(@ST,2) + ':' + RIGHT(@ST,2) + ':00')
SET @ENDDATE = DATEADD(s, 0, @ED + ' ' + LEFT(@ET,2) + ':' + RIGHT(@ET,2) + ':00')
SET @INTERVAL_TIME = DATEDIFF(s, @STARTDATE, @ENDDATE)
PRINT @STARTDATE
PRINT @ENDDATE
PRINT CAST((@INTERVAL_TIME / 3600.) AS varchar(10)) + ' Hrs'
Does this at least make sense?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 12, 2008 at 2:20 am
smunson (6/11/2008)
Please don't think that I'm making you responsible for a data collection faillure - most of us are put into positions of having to make do with less than ideal data.
No problem - AFAIK the field type for the various times varies depending on the source of the data, which is clearly something the aggregators need to address. For this reason it is probably best (for me) to work exclusively in DateTime.
My difficulty was understanding clearly exactly what you had to work with, and my impression was that you had two DateTime fields to work with, as opposed to separate date & time fields.
Now that I've got that mess straightened out in at least MY head...
Provided that each 4 digit time field has an associated date field,
Sadly, this is not the case. Try these (fictitious) data for example:
[font="Courier New"]
ArriveDate ArriveTime AssessTime StartTreatTime EndTreatTime LeaveTime
----------------------- ----------------------- ---------- -------------- ------------ ---------
2006/12/31 00:00:00.000 1900/01/01 22:21:00.000 2254 2308 0024 0026
2006/12/31 00:00:00.000 1900/01/01 22:25:00.000 2226 2228 0154 0154
2006/12/31 00:00:00.000 1900/01/01 22:37:00.000 2302 2341 2350 2351
2006/12/31 00:00:00.000 1900/01/01 22:49:00.000 2317 2356 0028 0028
2006/12/31 00:00:00.000 1900/01/01 23:02:00.000 0001 0016 0024 0025
2006/12/31 00:00:00.000 1900/01/01 23:16:00.000 2352 0014 0128 0148
[/font]
it would seem reasonably straightforward to be able to use STRING functions to tie the two together into something that will implicitly convert within the date arithmetic functions I referred to previously... or am I still confused?
Here's the concept, which may or may not match the data you had to work with:
DECLARE @STARTDATE DateTime, @ENDDATE DateTime
DECLARE @SD char(10), @ED char(10), @ST char(4), @et char(4)
SET @SD = '06/01/2008'
SET @ED = '06/02/2008'
SET @ST = '1345'
SET @et = '0215'
SET @STARTDATE = DATEADD(s, 0, @SD + ' ' + LEFT(@ST,2) + ':' + RIGHT(@ST,2) + ':00')
SET @ENDDATE = DATEADD(s, 0, @ED + ' ' + LEFT(@ET,2) + ':' + RIGHT(@ET,2) + ':00')
SET @INTERVAL_TIME = DATEDIFF(s, @STARTDATE, @ENDDATE)
PRINT @STARTDATE
PRINT @ENDDATE
PRINT CAST((@INTERVAL_TIME / 3600.) AS varchar(10)) + ' Hrs'
Does this at least make sense?
Steve
(aka smunson)
:):):)
I see, yes, it makes sense subject to the limitations above and the difference in date format between the US and UK 🙂
Thanks
Tim
Edited to make data easier to read
June 12, 2008 at 6:44 am
Now I see the difficulty. There's only one scenario where a solution is impossible - that of the end time being MORE than 24 hours beyond the start time. In that case, you have NO way to determine that such a time period did occur. Assuming that you tell those folks that limitation in advance, and indicate WHY it's entirely impossible, you CAN still just check to see if the end time's character string is less than the start time's character string, and if true, the date for the end time is just adding 1 day to the arrival date. Thanks again for helping me see this more clearly.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 12, 2008 at 7:05 am
Yeah
That should never happen.
But of course it has in the past. 🙁
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply