Solved

date convert function

Posted on 2003-12-05
8
341 Views
Last Modified: 2012-06-22
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
Comment
Question by:snowingnow
8 Comments
 
LVL 6

Accepted Solution

by:
lausz earned 30 total points
ID: 9883717
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
 
LVL 6

Expert Comment

by:lausz
ID: 9883746

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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9883996
@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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9884139
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
 
LVL 8

Expert Comment

by:dishanf
ID: 9886855
I think you can change the datatype while having data as varchar
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9886858
sorry You >> can't
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Select - find rows created in last 6 months 4 38
Webservices in T-SQL 3 31
SQL Server 2012 r2 - Sum totals 2 25
performance query 4 24
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question