Solved

date convert function

Posted on 2003-12-05
8
339 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
Comment Utility
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
Comment Utility

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
Comment Utility
@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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
I think you can change the datatype while having data as varchar
0
 
LVL 8

Expert Comment

by:dishanf
Comment Utility
sorry You >> can't
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now