July 24, 2006 at 10:05 am
Dear Friends,
Is it possible to convert or cast a varchar datatype field to datetime.
My table contains a field dateofbirth with varchar datatype how can it be converted to a datetime field without affecting the data.
Thanks.
July 24, 2006 at 10:17 am
This works fine for me (the bit in purple is the bit you need)...
--data
set nocount on
create table #t1 (dateofbirth varchar(30))
insert #t1
select '1 jan 2000'
union all select '24 jul 2006'
--calculation (alter the table column)
select * from #t1
alter table #t1 alter column dateofbirth datetime
select * from #t1
drop table #t1
/*results
dateofbirth
------------------------------
1 jan 2000
24 jul 2006
dateofbirth
------------------------------------------------------
2000-01-01 00:00:00.000
2006-07-24 00:00:00.000
*/
Make sure you do "select dateofbirth, cast(dateofbirth as datetime) from mytable" first and are happy with the results (i.e. it doesn't error and the values are as you'd like).
And make sure you have a backup in case it all goes wrong
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 24, 2006 at 10:23 am
Thanks for your reply....
I tried using cast and convert but did't work.
July 24, 2006 at 10:30 am
Okay - that sounds like there's some data in there which is not valid datetime data.
Try running...
select dateofbirth from mytable where isdate(dateofbirth) = 0
If there are some results, you'll have to fix that data first...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 25, 2006 at 3:29 am
If you are sure the format of your varchar, in your table, is always the same or implicitly convertible to a date (which it seems like it is not in your case), you are in luck. Then it is easy to convert the data as soon as you figure out the format and the appropriate conversion routine.
Give us an example of the format used in the table and we might be able to help you more.
Hanslindgren
July 25, 2006 at 4:25 am
Topic "Cast and Convert" in BOL must be really helpful for you.
_____________
Code for TallyGenerator
July 25, 2006 at 5:56 am
For datetime you better use convert instead of cast becasue for convert you can specify the format of your character data. See BOL for convert and there the style parameter
Bye
Gabor
July 25, 2006 at 6:13 am
But Shabu doesn't want to format data?
He wants to change the datatype of an existing varchar column to datetime.
He's got issues with doing that seemingly because there is some data in there which is not compatible. He should find and fix that data with the help of ISDATE, and then just alter the column.
Shabu - How are you getting on?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 25, 2006 at 6:28 am
The question is not about formating the data.
If you have a varchar column of format mm/dd/yyyy then you have to use convert(datetime, DateCol, 101) or if you have a format yyyy/mm/dd then you have to use convert(datetime, DateCol, 111) and so on
Bye
Gabor
July 25, 2006 at 10:38 am
Dear Friends,
RyanRandall is correct my problem is with the data in the table
the format of the date in the table is a mixture of mm/dd/yyyy and mm/dd/yy format.
IsDate(DOb)=0 returned many records.
Thanks for your valuable information
July 25, 2006 at 11:59 am
is weired that someone mixed data like that...
Take a look at SET DATEFORMAT and run that query again the problem might be that you preferences are in different format than the saved data so the conversion fails.
data is saved in MM/DD/YYYY and your preferences are DD/MM/YYYY causing some records to fail
Vasc
July 26, 2006 at 3:57 am
Hello!
This leads to quite a problem parsing your data. But if you know that these two are the ONLY types of dates inserted (somehow I would verify it VERY carefully) then I have a solution. (Once again be sure these are the only two formats and that no data in the form mm/dd/yy has been inserted as yy/mm/dd, example: what date does 01/02/03 correspond to? Or 03/02/01?)
Anyways, if the data is of the mm/dd/yyyy or mm/dd/yy format you can use DATETIMEFORMAT (as Vasc suggests) to convert properly or use the LEN(myVarcharDateColumn) to decide what other tactic you could use (UPDATE, INSERT INTO a new table with a DateTime column or creating a new datetime column and inserting into that).
Hint on SET DATETIMEFORMAT (look at the output of the last one):
SET DATEFORMAT DMY
SELECT ISDATE( '12/31/2077')
SELECT ISDATE( '12/31/77')
------------------------------
Output:
0
0
SET DATEFORMAT YMD
SELECT ISDATE( '12/31/2077')
SELECT ISDATE( '12/31/77')
------------------------------
Output:
1
0
SET DATEFORMAT MDY
SELECT ISDATE( '12/31/2077')
SELECT ISDATE( '12/31/77')
------------------------------
Output:
1
1
Hope this might help!
Hanslindgren
July 26, 2006 at 5:09 am
The real problem is that no matter what anyone does, you will not be able to differentiate between the two formats if both the day and month are <= 12. For example, someone born on 01/06/1970 (dd/mm/yyyy) will likely be interpreted as 01/06/1970 (mm/dd/yyyy) no matter which of the fine methods posted are used. There's simply no way to tell which format was used by looking at the data. Sure, you can make it convert to the datetime datatype, but some of the dates will be incorrect.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2006 at 6:52 am
If you're using the date for display this is how I accomplished that:
CONVERT(DATETIME,SUBSTRING(b.duedate,5,2)+ '-' + SUBSTRING(b.duedate,7,2) + '-' + SUBSTRING(b.duedate,1,4),101) + 30 AS duedate,
duedate in my case is a varchar(8). Just a thought. Haven't run into an issue where I'll get an invalid date, however I think you'll just get a NULL in the record if it doesn't format properly.
Thanks,
July 26, 2006 at 7:29 am
But that only means that your duedate varchar column always contains dates in the format YYYYMMDD...
Here the problem is that it is different for different records in the database.
Why do you store it as a VARCHAR(8) and not a CHAR(8) (saving space in the DB, makes more sense, does not need to keep track of stringlengths for every record).
Better yet. Why not a date? A date should always be a date, wouldn't we call it something else if not?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply