Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

date convert function

in SQL server 7, how can we change text data type to datetime type? like: 01/01/01 is text, i waant to chnge to 01/01/2001 as date data in a table. Thanks
0
snowingnow
Asked:
snowingnow
1 Solution
 
lauszCommented:
You can try something like this



select yourfield, case when right(yourfield, 2) > 10 then substring(yourfield,1,6) + '19' +  right(yourfield, 2)  else
substring(yourfield,1,6) + '20' +  right(yourfield, 2)  end
from yourtable
0
 
lauszCommented:

And to insert this value in a datetime field ..

create newtable( field1 varchar(10), field2 datetime)

insert into newtable
select yourfield, case when right(yourfield, 2) > 10 then convert(datetime,substring(yourfield,1,6) + '19' +  right(yourfield, 2),103)  else
convert(datetime,substring(yourfield,1,6) + '20' +  right(yourfield, 2) ,103) end
from yourtable
0
 
DexstarCommented:
@snowingnow:

> in SQL server 7, how can we change text data type to datetime type? like:
> 01/01/01 is text, i waant to chnge to 01/01/2001 as date data in a table. Thanks

You should just be able to use the CONVERT function.  Like this:
     CONVERT(DATETIME, '01/01/01')

In order to explain it any better, I need to know what you want to do with it.

Hope That Helps,
Dex*
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
you want to change the column type?

go into enterprise manager and
select the table.... right click
select design mode
highlight the relevant column
change the datatype to DATETIME.
0
 
Dishan FernandoSoftware Engineer / DBACommented:
I think you can change the datatype while having data as varchar
0
 
Dishan FernandoSoftware Engineer / DBACommented:
sorry You >> can't
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now