March 19, 2012 at 1:16 pm
Hello,
I am trying to convert a text field to a VARCHAR, which is not the problem, but I want to than convert the new VARchar column to datetime.
I keep getting unknown column error for my new column.
Any ideas?
Thanks.
March 19, 2012 at 1:27 pm
joe.cahoon (3/19/2012)
Hello,I am trying to convert a text field to a VARCHAR, which is not the problem, but I want to than convert the new VARchar column to datetime.
I keep getting unknown column error for my new column.
Any ideas?
Thanks.
Yeah I have some ideas.
Two questions, if you have a text field that contains datetime data why would first convert it to varchar? If your text field does not contain datetime data how would you expect to convert it?
If you can provide some details (ddl, sample data) we can take a look at the issue.
_______________________________________________________________
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/
March 19, 2012 at 2:25 pm
The field is (text,null) and the data reads as such 3/19/2012.
When I try to convert it, I am given an error stating that text cannot be explicitly converted to datetime.
I saw another website that varchar can be converted to datetime, that is the only reason for my attempt at converting it to varchar first.
Thanks,
Joe
March 19, 2012 at 2:32 pm
I forgot you can't convert from text straight to datetime, I haven't worked with text datatype in quite some time. I assume you are trying to get your data stored properly instead of a text field for datetime data?
What error(s) are you getting?
By chance this one?
Msg 242, Level 16, State 3, Line 10
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I was able to convert from text to datetime with no issues.
create table #test
(
Data text
)
insert #test select '3/19/2012'
select cast(CAST(Data as varchar(10)) as datetime) from #test
drop table #test
I suspect you either need to set your dateformat or you have some bad data in there.
_______________________________________________________________
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/
March 19, 2012 at 2:46 pm
Actually I am trying to pull data out of our database for a report, but because the dates I need are in a text field, I really cannot do much with them such as using them for a date range. But the way you just showed using CAST is something I have not tried yet. Believe me, I really wish whoever initially set up our database had thought about what they were doing before they did it :/
March 19, 2012 at 2:59 pm
Well if it is at all possible you should store your data in proper datatypes. Also the text datatypes is deprecated and will be going away. http://msdn.microsoft.com/en-us/library/ms187993.aspx
Keep in mind that if you have any rows where that column will not convert it will fail. Try the cast and let me know what happens. It can be done but sometimes it is really painful to work around this kind of thing.
_______________________________________________________________
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/
March 20, 2012 at 12:05 pm
Thanks! I tried the Cast you mentioned above and it worked. Now the only thing is that it gives me the date in this format: 2010-12-03 00:00:00.000. All I need is the date,not the time. So my next question is there a away to convert to say, 112 format from here.
Thanks again
March 20, 2012 at 12:09 pm
joe.cahoon (3/20/2012)
Thanks! I tried the Cast you mentioned above and it worked. Now the only thing is that it gives me the date in this format: 2010-12-03 00:00:00.000. All I need is the date,not the time. So my next question is there a away to convert to say, 112 format from here.Thanks again
DateTime is a datatype. That means it will always have a time component. Formatting is for display, not storage. If you want to remove that component for presentation, you have to do it at that time. If you upgrade to 2008 you would have the addition of date and time as separate datatypes.
_______________________________________________________________
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/
March 20, 2012 at 12:12 pm
Okay, so If I cannot convert from that format, is their a way to hide the time from being displayed?
March 20, 2012 at 12:17 pm
joe.cahoon (3/20/2012)
Okay, so If I cannot convert from that format, is their a way to hide the time from being displayed?
I think you maybe misunderstood what I was saying. Using format is for presentation. You can't store it without a timestamp.
declare @MyDateTime datetime
set @MyDateTime = '2010-12-03 00:00:00.000'
select @MyDateTime, CONVERT(varchar(10), @MyDateTime, 112) as FormattedForDisplay
Really presentation belongs at the presentation level but you can force it in sql with the convert.
_______________________________________________________________
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/
March 20, 2012 at 12:21 pm
Would that work for a whole column of dates, or would I have to declare each on and convert them individually?
March 20, 2012 at 12:24 pm
joe.cahoon (3/20/2012)
Would that work for a whole column of dates, or would I have to declare each on and convert them individually?
OH LORD NO!!! :w00t: I was just showing you as an example.
create table #MyDates
(
MyDateTime datetime
)
insert #MyDates
select '2010-12-03 00:00:00.000'
union all
select '2011-01-03 00:00:00.000'
select CONVERT(varchar(10), MyDateTime, 112) as FormattedForDisplay
from #MyDates
drop table #MyDates
_______________________________________________________________
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/
March 28, 2012 at 3:23 pm
Joe you can also try this:
DECLARE @table TABLE (MyDate Varchar(25))
insert @table
SELECT '3/28/2012'
SELECT CONVERT(VARCHAR(25),CONVERT(DATETIME,MyDate),112) FROM @table
Thanks
Benson Sibanda
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply