June 29, 2011 at 10:33 am
Hi All
I need help with converting varchar type to datetime but the format of the date e.g (01052011) and the column is varchar type, trying to insert data to another table but it's a datetime column.
I get the following error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
So i tried using convert or cast but none of them works in this case i'm using SQL 2008
CONVERT(DATE,[DIR_DIR_RES_DATE])
I get the following error:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Any help will be appreciated
It's better to fail while trying, rather than fail without trying!!!
June 29, 2011 at 10:42 am
This of course is exactly why you should use datetime datatypes to hold datetime data. Assuming your format is consistent you can use something like the following;
declare @val varchar(20) = '01052011'
select @val, cast(left(@val, 2) + '/' + SUBSTRING(@val, 2, 2) + '/' + RIGHT(@val, 4) as datetime)
I would definitely recommend changing your datatype to datetime. This of course is not always possible to we end being forced to fit a square peg in a round hole like above. Hope that helps.
_______________________________________________________________
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/
June 29, 2011 at 10:46 am
Thanks, it's just that the developer created the table with varchar type.
Thanks once more, will try the solution.
It's better to fail while trying, rather than fail without trying!!!
June 29, 2011 at 10:50 am
So slap your developer and make him change it to an appropriate datatype. 😛
_______________________________________________________________
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/
June 29, 2011 at 10:54 am
😀
Please bear with me, so i'm selecting data from the developer table with incorrect data type inserting data to a table with correct data types. So how do i incorporate the solution provided
e.g insert into table (column list)
select (column list) from table, as i have 6 columns with date
Tx
It's better to fail while trying, rather than fail without trying!!!
June 29, 2011 at 10:59 am
insert into [good_table]
(col1, col2, col3)
select
colA,
colB,
cast(left([ColC], 2) + '/' + SUBSTRING([ColC], 2, 2) + '/' + RIGHT([ColC], 4) as datetime)
FROM [dumb_table]
Something like that
June 29, 2011 at 11:01 am
YUCK!!!! After you catch your breath for beating your dev senseless....you will have to convert each of your columns. Assuming they are in the same format you can just add all the nasty string concatentation for each column.
The following example uses a table to maybe make it easier to understand what you need to do.
;with cte as --just creating a quick inline table
(
select '01052011' as datecol
)
--insert [YourTable]
select cast(left(datecol, 2) + '/' + SUBSTRING(datecol, 2, 2) + '/' + RIGHT(datecol, 4) as datetime) as DateCol1
from cte
_______________________________________________________________
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/
June 29, 2011 at 11:21 am
run this statement to see the result:
SELECT CAST(STUFF(STUFF('01052011',5,0,'-'),3,0,'-') AS DATETIME)
In your select query use the above statement replacing '01052011' with your column name.
The other issue you may run into is the data being incorrect since its a varchar it may contain junk data.
So here are 2 examples to demonstrate catching bad data, run these statements to see the result.
SELECT CASE WHEN ISDATE(STUFF(STUFF('01052011', 5, 0, '-'), 3, 0, '-')) = 1
THEN CAST(STUFF(STUFF('01052011', 5, 0, '-'), 3, 0, '-') AS DATETIME)
ELSE '1/1/1900'
END AS GoodDate
go
SELECT CASE WHEN ISDATE(STUFF(STUFF('9901052011', 5, 0, '-'), 3, 0, '-')) = 1
THEN CAST(STUFF(STUFF('01052011', 5, 0, '-'), 3, 0, '-') AS DATETIME)
ELSE '1/1/1900'
END AS GoodDate
again in your select statement use the entire case statement from the word CASE to the word END and replace the '01052011' with your varchar column name.
And don't beat up the developer, it's a waste of energy, if data was always correct a monkey could do our jobs....
June 29, 2011 at 11:33 am
Thanks for your help, i will try the solution in the morning.
It's better to fail while trying, rather than fail without trying!!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply