MS SQL Server - SQL 2000 - Convert varchar to datetime or smalldatetime

Author

SQL 2000 - Convert varchar to datetime or smalldatetime

jdoggz

2006-10-24, 6:30 pm

I have a field that contains the following: 091506. That field
represents a date. I would like to convert it to datetime in a SQL
query. Can anyone tell me how? The field will contain a different
date everyday, but it will still be the same length.

Thanks!

Arnie Rowland

2006-10-24, 6:30 pm

Unfortunately, someone made a bad decision about how to store a datetime
value. Now, until the database can be corrected, you will have to
continually use kludges to deal with it. I recommend changing the column to
a datetime datatype as soon as is practical.

The datetime datatype is optimized for storage of date and time values.
There are even a number of specialty functions to work with datetime values,
and datetime arithmetic is easy with datetime values. Try adding 30 days to
a value in your column. Did the month change? What happens when the month is
Feb -or Aug? You will always have difficulty working with datetime values
stored like this. Bad dog, sit! (Now we continue with your regularly
scheduled programming...)

So, until you can make the base corrections to the database, here is a
kludge:

SET dateformat 'mdy'
SELECT cast( stuff( stuff( '091506', 3, 0, '/' ), 6, 0, '/' ) as datetime )

Of course, in a query, the '091506' can be replaced with the column name.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"jdoggz" < brownsugareve2003@ya
hoo.com> wrote in message
news:1158878725.547642.263620@k70g2000cwa.googlegroups.com...
>I have a field that contains the following: 091506. That field
> represents a date. I would like to convert it to datetime in a SQL
> query. Can anyone tell me how? The field will contain a different
> date everyday, but it will still be the same length.
>
> Thanks!
>

jdoggz

2006-10-24, 6:30 pm

Well, the good news is that it's my database, and when I set the column
as datetime, it bombs out with the message: ODBC error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow
error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.

I'll try your query to see if that helps.
Thanks!

Arnie Rowland wrote:[color=darkred
]
> Unfortunately, someone made a bad decision about how to store a datetime
> value. Now, until the database can be corrected, you will have to
> continually use kludges to deal with it. I recommend changing the column to
> a datetime datatype as soon as is practical.
>
> The datetime datatype is optimized for storage of date and time values.
> There are even a number of specialty functions to work with datetime values,
> and datetime arithmetic is easy with datetime values. Try adding 30 days to
> a value in your column. Did the month change? What happens when the month is
> Feb -or Aug? You will always have difficulty working with datetime values
> stored like this. Bad dog, sit! (Now we continue with your regularly
> scheduled programming...)
>
> So, until you can make the base corrections to the database, here is a
> kludge:
>
> SET dateformat 'mdy'
> SELECT cast( stuff( stuff( '091506', 3, 0, '/' ), 6, 0, '/' ) as datetime )
>
> Of course, in a query, the '091506' can be replaced with the column name.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "jdoggz" < brownsugareve2003@ya
hoo.com> wrote in message
> news:1158878725.547642.263620@k70g2000cwa.googlegroups.com...

Arnie Rowland

2006-10-24, 6:30 pm

If you desire to change the field to datetime, I suggest adding a new field
to the table (datetime), then using the expression I provided, populate the
new field. Once completed, drop the old field and rename the new one to the
name of the one dropped.

Of course, if any of the values fail to convert, you will have to locate
them and correct them in order to complete the process.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"jdoggz" < brownsugareve2003@ya
hoo.com> wrote in message
news:1158932615.980091.265120@i42g2000cwa.googlegroups.com...
> Well, the good news is that it's my database, and when I set the column
> as datetime, it bombs out with the message: ODBC error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow
> error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>
> I'll try your query to see if that helps.
> Thanks!
>
> Arnie Rowland wrote:
>

Sponsored Links