September 26, 2012 at 8:20 am
I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks like this: 1030 or 0735. I need to be able to calculate a difference to determine a wait time. I've tried cast and convert, I keep getting an error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. I was thinking integers?
I really haven't a clue where to go from here. Please help.
September 26, 2012 at 8:25 am
Can you post some of the values which are in the columns so that we can try and find a solution.
September 26, 2012 at 8:26 am
nicoleaslater (9/26/2012)
I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks like this: 1030 or 0735. I need to be able to calculate a difference to determine a wait time. I've tried cast and convert, I keep getting an error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. I was thinking integers?I really haven't a clue where to go from here. Please help.
The struggles you are facing is EXACTLY why you should always use the proper datatypes. Datetime data should always be stored in a datetime column. Of course this is sometimes way beyond our control and we have to plug our noses and push forward. So in the name of plugging my nose and helping I need to get some more details from you.
Can you post ddl (create table statements) and some sample data (insert statements) along with desired output based on your sample data. You do not need to post a lot of data but at least a few rows to demonstrate your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2012 at 8:28 am
How are you trying to calculate difference?
Is the Appt Time should be taken as for the same date as one in the column which contains date and time?
What is really your wait time?
Is this any help to you:
DECLARE @dt DATETIME, @appttime CHAR(4)
SELECT @dt = GETDATE(), @appttime = '0730'
SELECT @dt
,@appttime
,DATEADD(MINUTE, CAST(RIGHT(@appttime,2) AS INT)
,DATEADD(HOUR, CAST(LEFT(@appttime,2) AS INT), @dt))
September 26, 2012 at 8:42 am
This is a medical records things sp the amount of info I can give out is very limited. We use too many different third party apps that create their own dbs in sql and I've inherited these puppies. We are trying to determine how long a patient waits from the time they are 'checked in' until they are taken to an exam room and until the doc comes in. All those fields are stored in datetime because we created them correctly and I have no problem with that. Now I am tasked with figuring out the difference between the scheduled appt time which is stored in a different db as varchar and the time of checkin (datetime). My results of querying this table might look like this:
check_in appt_time
1911-03-02 14:00:00.0001300
1911-03-02 14:00:00.0000950
I need to do math with this. Am I asking the impossible?
September 26, 2012 at 8:44 am
This might make more sense. Formatting is never my thing.
check_in
1911-03-02 14:00:00.000
1911-03-02 14:00:00.000
appt_time
1300
0950
September 26, 2012 at 9:12 am
nicoleaslater (9/26/2012)
This might make more sense. Formatting is never my thing.check_in
1911-03-02 14:00:00.000
1911-03-02 14:00:00.000
appt_time
1300
0950
No you are not asking the impossible but you did not post this like I suggested (ddl and sample data). In order to make this work we need something that we can copy and paste into SSMS so we can work on your problem instead of spending time setting up the problem. I know you are new around here so I did this for you as an example.
Here is your data is an easily consumable format.
create table #CheckIn
(
check_in datetime,
appt_time char(4)
)
insert #CheckIn
select '1911-03-02T14:00:00.000', '1300' union all
select '1911-03-02T14:00:00.000', '0950'
select *
from #CheckIn
The advantage is two fold. The volunteers that are going to help with your issue don't have to write this first. Instead they can spend their time working on the issue at hand with little setup effort. Secondly, there is no doubt about datatype and such.
OK so what so I demonstrated how you should post questions in the future. What you really care about is some help with your solution.
Try this.
;with cte as
(
select check_in, appt_time, dateadd(n, CAST(right(appt_time, 2) as int), dateadd(hh, cast(left(appt_time, 2) as INT), dateadd(dd, datediff(dd, 0, check_in), 0))) as AppointmentTime
from #CheckIn
)
select *, DATEDIFF(n, check_in, AppointmentTime) from cte
I think that is what you are looking for, or at least it is reasonably close.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2012 at 9:19 am
I AM new, and I am grateful for your help. I am actually quite new at query writing as well and I often find exactly what I am looking for on this forum without asking a question, so this was my first go. I see what you are saying for next time.
For now, thank you. What you wrote is pretty close to what I am talking about. I have been playing with converting these to integers but I am still getting conversion errors. I think utilizing your solution, which is a bit more complex than mine, will work for me. Thanks again, hopefully I won't be back with more questions. 😉
September 26, 2012 at 9:27 am
nicoleaslater (9/26/2012)
I AM new, and I am grateful for your help. I am actually quite new at query writing as well and I often find exactly what I am looking for on this forum without asking a question, so this was my first go. I see what you are saying for next time.For now, thank you. What you wrote is pretty close to what I am talking about. I have been playing with converting these to integers but I am still getting conversion errors. I think utilizing your solution, which is a bit more complex than mine, will work for me. Thanks again, hopefully I won't be back with more questions. 😉
No problems. You should not feel guilty about asking questions when you need help. But you seem to be someone who is willing and/or desires to figure it out on your own. That is excellent and you always learn more from making mistakes than getting it right. 🙂 If you do need more help in the future don't hesitate. Now you know the type of format to post in. Keep in mind that especially in your field you don't have to post the full tables and the should always be generic.
Give a shout back here if you need some help tweaking your code into the final shape.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2012 at 8:29 pm
Nice set up data Sean!
Using it, I'd like to offer a slightly less verbose solution:
SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(appt_time, 3, 0, ':') AS TIME))
FROM #CheckIn
The STUFF apparently forces the datatype to VARCHAR so minutes aren't lost.
Both solutions return negative minutes, so you may want to take that into account.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 26, 2012 at 9:04 pm
CELKO (9/26/2012)
I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks like this: 1030 or 0735. I need to be able to calculate a difference to determine a wait time.
Wish we had DDL, but the first nameless column is DATETIME2(0) and the second one TIME(0) or another DATETIME2(0) with the complete timestamp in it. If you use TIME(0), then you can use "CAST (nameless AS DATE) + appointment_time" to get the second argument for a DATEDIFF() call.
Interesting approach. CASTing to TIME just seemed a little simpler as presumably they won't keep their patients waiting for more than a day. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2012 at 7:15 am
dwain.c (9/26/2012)
Nice set up data Sean!Using it, I'd like to offer a slightly less verbose solution:
SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(appt_time, 3, 0, ':') AS TIME))
FROM #CheckIn
The STUFF apparently forces the datatype to VARCHAR so minutes aren't lost.
Both solutions return negative minutes, so you may want to take that into account.
That's pretty cool Dwain. Didn't think of using stuff, I went the hard away around. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2012 at 1:08 pm
Hi,
This is vivek, how to change string to int.may be you can change date/int to char.
Thanks,
vivek
October 2, 2012 at 11:20 am
BEGIN
DECLARE @return_dt DATETIME;
SET @return_dt = CAST(CONVERT(varchar(11), CAST(@appt_date AS DATETIME), 120)
+ ' ' + SUBSTRING(@appt_time, 1, 2)
+ ':' + SUBSTRING(@appt_time, 3, 2)
+ ':00' AS DATETIME)
RETURN @return_dt
END
GO
Returns datetime. Thanks all for your help.
October 2, 2012 at 4:26 pm
dwain.c (9/26/2012)
Nice set up data Sean!Using it, I'd like to offer a slightly less verbose solution:
SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(appt_time, 3, 0, ':') AS TIME))
FROM #CheckIn
The STUFF apparently forces the datatype to VARCHAR so minutes aren't lost.
Both solutions return negative minutes, so you may want to take that into account.
I think it needs one (semi)slight adjustment:
SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(RIGHT('000' + CAST(appt_time AS varchar(4)), 4), 3, 0, ':') AS TIME))
FROM #CheckIn
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply