Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 28th, 2003, 12:35 AM
Registered User
 
Join Date: Jul 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Change data type to DATE in SQL

Does anyone knows how to change the data type to Date, which is currently in Text. I tried to do it in Microsoft Access but all the Records will be deleted. I guess i got to use some SQL commands.
Can anyone guide me? Thanks in advance.

 
Old July 28th, 2003, 12:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The bigger issue is how are you going to convert your text data to date data. Unless it is in an readable format then you will have problems.

Also this forum is dedicated to SQL Server. An Access forum may better Access Specifiec suggestions.

regards
David Cameron
 
Old July 28th, 2003, 01:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

CAST('TEXT' AS datetime) ex: SELECT CAST('2003-2-3' AS datetime)

Always:),
Hovik Melkomian.
 
Old July 28th, 2003, 10:23 PM
Registered User
 
Join Date: Jul 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

But i have thousands of records..how can i change the data type of that field in 1 shot?

 
Old July 28th, 2003, 10:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code:
ALTER TABLE MyTable ADD MyNewDateCol datetime
GO
UPDATE MyTable SET MyNewDateCol = CAST(MyOldCol AS datetime)
regards
David Cameron





Similar Threads
Thread Thread Starter Forum Replies Last Post
Data type change from int to Varchar getxyz SQL Server 2000 1 April 29th, 2005 09:59 AM
Getting Problem in DATE/TIME Data type in MS Acces selam70 Classic ASP Databases 2 September 4th, 2004 02:31 PM
data type for date in msde isheikh SQL Server ASP 1 March 9th, 2004 10:20 PM
change data type to date kherboon Access 2 July 29th, 2003 10:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.