Prev: Query LDAP user group membership from SQL Server
Next: SQL 2005 Mirroring Databases
From: Patrice on 14 Sep 2005 12:17
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!

From: Aaron Bertrand [SQL Server MVP] on 14 Sep 2005 12:24
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(a)discussions.microsoft.com> wrote in message
news:BCAB2D7A-D6A9-4952-81C7-39114E913AE2(a)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!
>


From: Patrice on 14 Sep 2005 13:23
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 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(a)discussions.microsoft.com> wrote in message
> news:BCAB2D7A-D6A9-4952-81C7-39114E913AE2(a)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!
> >
>
>
>
From: Aaron Bertrand [SQL Server MVP] on 14 Sep 2005 13:31
> 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


From: Patrice on 14 Sep 2005 15:08
You are so very right. Problem solved - thank you kindly!

"Aaron Bertrand [SQL Server MVP]" wrote:

> > 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
>
>
>
 | 
Pages: 1
Prev: Query LDAP user group membership from SQL Server
Next: SQL 2005 Mirroring Databases