May 6, 2011 at 5:37 am
Hey,
How do I convert (n)(var)char to time? I tried several ways including a simple:
SELECT CONVERT(time,'143825',108)
But it always gives me this error:
Conversion failed when converting date and/or time from character string.
May 6, 2011 at 5:50 am
How about '14:38:25'?
May 6, 2011 at 5:51 am
Gwandi (5/6/2011)
Hey,How do I convert (n)(var)char to time? I tried several ways including a simple:
SELECT CONVERT(time,'143825',108)But it always gives me this error:
Conversion failed when converting date and/or time from character string.
Does it work with a valid time
ie
SELECT CONVERT(time,'12:15',108)
?
Ian
May 6, 2011 at 5:58 am
Yeah it does. But the problem is that I am getting the values from an imported XML file and I can´t add the ´:´.
May 6, 2011 at 6:21 am
That should work but the problem is I'm not sure how to add that to the select query (I'm still kinda new to all this). My query currently looks like this:
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM dbo.TempTable
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc, '/FlexQueryResponse/FlexStatements/FlexStatement/Trades/Trade')
WITH (accountId NVARCHAR(50) '@accountId', etc, etc)
EXEC sp_xml_removedocument @hDoc
May 6, 2011 at 6:25 am
replace select * with the list of columns, then use the stuff function on that column rather than the variable I use in my demo.
May 6, 2011 at 6:33 am
Okay that worked. Thanks a lot 🙂
May 6, 2011 at 6:39 am
The code below works for what you are asking, if you are converting an entire column that has data like '143825' just replace the '143825' with the column name and it'll do the same
SELECT ' ' + substring('143825', 1, 2)
+ ':' + substring('143825', 3, 2)
+ ':' + substring('143825', 5, 2)
Output: 14:38:25
This is the code I used to test it on a table that had all numbers in the column:
SELECT ' ' + substring([Date], 1, 2)
+ ':' + substring([Date], 3, 2)
+ ':' + substring([Date], 5, 2) from DateTest
Output: 14:38:25
This link was super helpful: http://www.sqlusa.com/bestpractices/datetimeconversion/
May 29, 2015 at 7:01 am
Hello friends,
how can I get time difference of the following record please help,
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
Operand data type time is invalid for subtract operator.
May 29, 2015 at 7:15 am
pspkshah (5/29/2015)
Hello friends,how can I get time difference of the following record please help,
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
Operand data type time is invalid for subtract operator.
You should start your own thread instead of hijacking another thread that is 4 years old.
When you want to do math on date or datetime you should use DATEADD. You would do yourself a huge favor by using the time datatype instead of storing time information in a varchar column.
--EDIT--
I see that you did in fact start your own thread for this. http://www.sqlservercentral.com/Forums/Topic1689898-391-1.aspx
_______________________________________________________________
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/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply