Access upgrade- Convert varchar to date
-
I may be wrong and this needs to be in the newbie area, but here goes.
I have a large Access database that Ive converted to SQL. The conversion went ok. I have a field in the main table that listed dates of birth mm/dd/yy. The conversion made it a varchar. Originally, I had a query that searched for dates of birth. I would like to convert this field to a date using mm/dd/yyyy. Mostly to remain uniform with everything. What is the eaisiest way to add the "19" infront of all the year dates and change the field to a date field?
Any and all help is greatly appreciated
Tim
-
You should backup your database before trying this ...
In Enterprise Manager, just make a design change to the datatype of the varchar field (change it to 'datetime') and save. You will get a warning message about the possibility of losing data - click OK. SQL Server will change the field to a datetime and convert the text dates to 'proper' datetime dates at the same time.
If the above looks a bit 'quick and dirty', you could always add a new datetime field to the main table, write a suitable UPDATE query to copy the data from the varchar date field to it (and CAST it to a date in the process). Then just delete the varchar date field and use the newly created date field instead (renaming it as required).
Good luck!
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
didnt work, I got an error that says some of the dates are out of date range. Ive got about 724 records with DOB's in Employees (table) DOB(field) and they are listed like 07/04/04.
Ive seen a select statement using
SELECT CONVERT(varchar(10),DOB,102) FROM Employees
but im not sure how to enter it when I use the stored proceedure wizard.
Is this heading in the right direction? or am I stuck with a varchar and having to put 19 in front of 700 records by hand?
Thanks
-
Under no circumstances should you enter '19' by hand - that's what computers are for
Also, you wouldn't be able to easily take advantage of any special date processing functions (eg add a day, add a year etc) if you have the dates stored in a text field.Try the following:
1) Create a new datetime field in your SQL Server Employees table - DateOfBirth say.
2) Write a query to update it - something like this
update employees
set DateOfBirth = CONVERT(varchar(10),DOB,102)
3) Delete your DOB field
4) Rename the DateOfBirth field as DOB and that should do it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The CONVERT statement should be
SET DateOfBirth =CONVERT(SmallDateTime,DOB,1)
This is because you are converting text to a date. The final argument identifies the string as being in mm/dd/yy format.
To display it in your mm/dd/yyyy format after it has populated your database then use
SELECT CONVERT(CHAR(10),DateOfBirth,101) FROM etc.
Adding 100 to the final parameter tells SQL to use four digit years.
-
You are absolutely right, David.
Only one note: using of SmallDateTime for DateOfBirth is little risky.
There are more than 25000 people in Japan older than 100 years. Thier DOB could not be converted to SmallDateTime.
Of course, McDonalds prevents it in USA, but what if...
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply