Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

varchar to time or datetime or ARGGHH Please help me. Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:26 AM
Points: 11, Visits: 19
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.
Post #1364722
Posted Wednesday, September 26, 2012 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
Can you post some of the values which are in the columns so that we can try and find a solution.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1364728
Posted Wednesday, September 26, 2012 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1364733
Posted Wednesday, September 26, 2012 8:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:09 AM
Points: 2,873, Visits: 5,183
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))




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1364737
Posted Wednesday, September 26, 2012 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:26 AM
Points: 11, Visits: 19
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.000 1300
1911-03-02 14:00:00.000 0950

I need to do math with this. Am I asking the impossible?
Post #1364746
Posted Wednesday, September 26, 2012 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:26 AM
Points: 11, Visits: 19
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
Post #1364749
Posted Wednesday, September 26, 2012 9:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1364775
Posted Wednesday, September 26, 2012 9:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 27, 2014 9:26 AM
Points: 11, Visits: 19
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.
Post #1364779
Posted Wednesday, September 26, 2012 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1364789
Posted Wednesday, September 26, 2012 8:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1365004
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse