Convert Char column to Datetime column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert Char column to Datetime column

Author

Topic

BDesigns
Starting Member

12 Posts

Posted - 2006-11-16 : 15:54:38

Hey guys, I know this is a basic question but I have tried a lot of things. I imported data from an excel spreadsheet into a 'char' column called 'dob' (aka date of birth) since it wouldn't allow me to import the dates into a regular datetime column.

So I have my dates that are formatted as 'mmddyyyy' and have tried the following and always get this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

I even tried checking my regional settings in Enterprise Manager and they are correct (aka English) and everything is a local database/server on my machine.

Here is what I have tried, I created a new column that is datetime formatted called 'dob2' and have received the same error message.

--------


SELECT CONVERT(CHAR(10), dob, 112) from vrpg_new_patient_relationship

update vrpg_new_patient_relationship set dob2 = convert(datetime, dob) 

update vrpg_new_patient_relationship
set dob2=convert(datetime,convert(char(10),dob,101))

update dbo.vrpg_new_patient_relationship
set dob = convert(char(10), convert(datetime, dob2, 3),
112)

update vrpg_new_patient_relationship
set dob2 = convert(datetime, left(dob,10)) 

update vrpg_new_patient_relationship
  set dob2 = cast (dob as datetime) 

SELECT CAST(dob AS datetime) from vrpg_new_patient_relationship

Help?

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-16 : 16:11:56

[code]
CREATE FUNCTION TO_DATE
(@col varchar(8))
RETURNS datetime
AS
BEGIN
RETURN RIGHT(@col,4)+LEFT(@col,2)+SUBSTRING(@Col,3,2)
END

SELECT dbo.TO_DATE('10241960')

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

BDesigns
Starting Member

12 Posts

Posted - 2006-11-16 : 16:20:12

[quote]Originally posted by X002548


CREATE FUNCTION TO_DATE
	(@col varchar(8))
RETURNS datetime
AS
  BEGIN
	RETURN RIGHT(@col,4)+LEFT(@col,2)+SUBSTRING(@Col,3,2)
  END

SELECT dbo.TO_DATE('10241960')

Nevermind I found out what my real issue was, there were some DOB that had '00000000' instead of an actual date of birth, so it was a data issue.

The above worked great, thank you very much!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 16:23:53

update vrpg_new_patient_relationship set dob2 = dbo.TO_DATE(dob)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources