Convert varchar to datetime

Hi,
I have what seems to be a simple issue, yet I can't get it to work:
I have a table that I have imported into SQL via a .txt file , one of the
fields holds a date (which was originally in the format of '122499'). I
added, based on certain criteria a '19' or '20' in front of the year to make
it '12241999' (still in a varchar type). But now when I try to insert it
into my final table which has a datatype as datetime, I'm having
difficulties, even if I try to convert or cast to datetime, I get the error
of:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
I have read that if it is not in the default sql datetime format, this error
will appear, but I do seem to have it in the default order of mm/dd/yyyy.
Any suggestions?
Thank you in advance!Transpose the year to the beginning of the string. Instead of using CONVERT
just say:
RIGHT(col_name, 4) + LEFT(col_name, 4)
YYYYMMDD is the recommended format to guarantee. All these other format
(mmddyy, mmddyyyy, m/d/y, d/m/y, etc.) are completely and utterly for the
birds.
Please see http://www.karaszi.com/SQLServer/info_datetime.asp
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:BCAB2D7A-D6A9-4952-81C7-39114E913AE2@.microsoft.com...
> Hi,
> I have what seems to be a simple issue, yet I can't get it to work:
> I have a table that I have imported into SQL via a .txt file , one of the
> fields holds a date (which was originally in the format of '122499'). I
> added, based on certain criteria a '19' or '20' in front of the year to
> make
> it '12241999' (still in a varchar type). But now when I try to insert it
> into my final table which has a datatype as datetime, I'm having
> difficulties, even if I try to convert or cast to datetime, I get the
> error
> of:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> I have read that if it is not in the default sql datetime format, this
> error
> will appear, but I do seem to have it in the default order of mm/dd/yyyy.
> Any suggestions?
> Thank you in advance!
>|||Hi Aaron,
I tried what you suggested and it does transpose the date in my table, of
course, but I still get the same error when trying to insert that field into
my fact table which has a datetime datatype - do I still need to convert or
cast that field into datetime datatype?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Transpose the year to the beginning of the string. Instead of using CONVE
RT
> just say:
> RIGHT(col_name, 4) + LEFT(col_name, 4)
> YYYYMMDD is the recommended format to guarantee. All these other format
> (mmddyy, mmddyyyy, m/d/y, d/m/y, etc.) are completely and utterly for the
> birds.
> Please see http://www.karaszi.com/SQLServer/info_datetime.asp
>
>
> "Patrice" <Patrice@.discussions.microsoft.com> wrote in message
> news:BCAB2D7A-D6A9-4952-81C7-39114E913AE2@.microsoft.com...
>
>|||> I tried what you suggested and it does transpose the date in my table, of
> course, but I still get the same error when trying to insert that field
> into
> my fact table which has a datetime datatype - do I still need to convert
> or
> cast that field into datetime datatype?
No, but you should check if any of the data was bogus (my guess is there is
at least one) by SELECT COUNT(*) FROM OriginalTable WHERE ISDATE(Column) = 0
Just because you transpose 13131999 to 19991313 does not make it a valid
date. :-)
And thus, you see why date/time should always be stored as such, and never
as char.
A|||You are so very right. Problem solved - thank you kindly!
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, but you should check if any of the data was bogus (my guess is there i
s
> at least one) by SELECT COUNT(*) FROM OriginalTable WHERE ISDATE(Column) =
0
> Just because you transpose 13131999 to 19991313 does not make it a valid
> date. :-)
> And thus, you see why date/time should always be stored as such, and never
> as char.
> A
>
>