Converting varchar to a datetime datatype

Question

Post reply

Converting varchar to a datetime datatype

Shabu George

SSC Journeyman

Points: 91
More actions
July 24, 2006 at 10:05 am

#114307

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.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

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.

Shabu George SSC Journeyman Points: 91 More actions · Answer 2

Thanks for your reply....

I tried using cast and convert but did't work.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

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.

Hans Lindgren SSChampion Points: 10494 More actions · Answer 4

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

Sergiy SSC Guru Points: 110192 More actions · Answer 5

Topic "Cast and Convert" in BOL must be really helpful for you.

_____________
Code for TallyGenerator

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

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

RyanRandall SSChampion Points: 13623 More actions · Answer 7

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.

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

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

Shabu George SSC Journeyman Points: 91 More actions · Answer 9

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

Vasc SSCarpal Tunnel Points: 4623 More actions · Answer 10

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

Kindest Regards,

Vasc

Hans Lindgren SSChampion Points: 10494 More actions · Answer 11

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

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

Hope this might help!

Hanslindgren

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Joe Contreras Ten Centuries Points: 1341 More actions · Answer 13

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,

Hans Lindgren SSChampion Points: 10494 More actions · Answer 14

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?