how to convert varchar into datetime field

9 Jun 2006 3:44 PM

TG

Hi!

I have the following field:

e.g

this field is called admit
60124 varchar 255 (type length)

I want to convert it to a date.

e.g

20060124

How can I achieve this?

I tried using

update special
set admit_date = convert(varchar(5),admit,101)

Thank you!

9 Jun 2006 3:49 PM

SQL Menace

How do you know what century this is?

this will create the date for you assuming it's 21st century

declare @i int
select @i =60124
select convert(datetime,(convert(varchar(8),20000000 + @i )))

Denis the SQL Menace
http://sqlservercode.blogspot.com/

TG wrote:
Show quote

> Hi!
>
> I have the following field:
>
> e.g
>
>
> this field is called admit
> 60124 varchar 255 (type length)
>
>
>
> I want to convert it to a date.
>
> e.g
>
> 20060124
>
>
>
> How can I achieve this?
>
> I tried using
>
>
> update special
> set admit_date = convert(varchar(5),admit,101)
>
> Thank you!

9 Jun 2006 3:57 PM

TG

Okay, so how can I update the whole column without specifying the field
as 60124...since all records have different values?

Thanks!

SQL Menace wrote:
Show quote

> How do you know what century this is?
>
> this will create the date for you assuming it's 21st century
>
> declare @i int
> select @i =60124
> select convert(datetime,(convert(varchar(8),20000000 + @i )))
>
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> TG wrote:
> > Hi!
> >
> > I have the following field:
> >
> > e.g
> >
> >
> > this field is called admit
> > 60124 varchar 255 (type length)
> >
> >
> >
> > I want to convert it to a date.
> >
> > e.g
> >
> > 20060124
> >
> >
> >
> > How can I achieve this?
> >
> > I tried using
> >
> >
> > update special
> > set admit_date = convert(varchar(5),admit,101)
> >
> > Thank you!

9 Jun 2006 4:00 PM

Aaron Bertrand [SQL Server MVP]

Change @i to ColumnName

Show quote

"TG" <jtam***@yahoo.com> wrote in message
news:1149868656.994970.323060@u72g2000cwu.googlegroups.com...
> Okay, so how can I update the whole column without specifying the field
> as 60124...since all records have different values?
>
> Thanks!
>
>
>
> SQL Menace wrote:
>> How do you know what century this is?
>>
>> this will create the date for you assuming it's 21st century
>>
>> declare @i int
>> select @i =60124
>> select convert(datetime,(convert(varchar(8),20000000 + @i )))
>>
>>
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>> TG wrote:
>> > Hi!
>> >
>> > I have the following field:
>> >
>> > e.g
>> >
>> >
>> > this field is called admit
>> > 60124 varchar 255 (type length)
>> >
>> >
>> >
>> > I want to convert it to a date.
>> >
>> > e.g
>> >
>> > 20060124
>> >
>> >
>> >
>> > How can I achieve this?
>> >
>> > I tried using
>> >
>> >
>> > update special
>> > set admit_date = convert(varchar(5),admit,101)
>> >
>> > Thank you!
>

9 Jun 2006 4:06 PM

SQL Menace

First check for bad date, you can use the ISDATE function to do that
select * from table where isdate(convert(varchar(8),20000000 + admit ))
=0

example
declare @i int
select @i =60124
select isdate(convert(varchar(8),20000000 + @i ))

If you have bad date make the value NULL

Then do your update
update table
set admit = convert(datetime,(convert(varchar(8),20000000 + admit )))

after all that make the column a datetime column

Denis the SQL Menace
http://sqlservercode.blogspot.com/

TG wrote:
Show quote

> Okay, so how can I update the whole column without specifying the field
> as 60124...since all records have different values?
>
> Thanks!
>
>
>
> SQL Menace wrote:
> > How do you know what century this is?
> >
> > this will create the date for you assuming it's 21st century
> >
> > declare @i int
> > select @i =60124
> > select convert(datetime,(convert(varchar(8),20000000 + @i )))
> >
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> > TG wrote:
> > > Hi!
> > >
> > > I have the following field:
> > >
> > > e.g
> > >
> > >
> > > this field is called admit
> > > 60124 varchar 255 (type length)
> > >
> > >
> > >
> > > I want to convert it to a date.
> > >
> > > e.g
> > >
> > > 20060124
> > >
> > >
> > >
> > > How can I achieve this?
> > >
> > > I tried using
> > >
> > >
> > > update special
> > > set admit_date = convert(varchar(5),admit,101)
> > >
> > > Thank you!