Convert varchar to date - SQL Server Forums

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 varchar to date

Author

Topic

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-02 : 12:19:14

I have a column in a table that has varchar dates in it "MM/DD/YY"
I need to convert it to a datetime.

I have tried convert(datetime,dte,1) and get date overflow. I have looked and search and can't find anything that will fix it. I have also tried cast()

Thank God for Forums.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-02 : 12:41:49

if convert(datetime,dte,1) gives an error it probably means that one of the rows has an invalid date.
Before 1752 or whatever or too big?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-02 : 12:49:49

I dont think the dates are to big.
This is my dates
03/13/41 09/06/83
04/16/52 03/29/79
09/07/50 02/12/92
04/30/62 02/26/92
04/11/61 04/10/92
06/25/54 06/01/92
03/27/65 10/03/95
12/02/56 07/07/78
10/14/48 06/11/90
01/18/42 01/07/81
04/07/59 11/15/91

This is my error message
Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Thank God for Forums.

mcp111
Starting Member

44 Posts

Posted - 2003-04-02 : 13:06:16

use select convert(datetime,'3/13/41',1)

this works fine!

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-02 : 13:17:50

OK that might work but I need to run a script that will do that on about 3500 records.

Thank God for Forums.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-02 : 13:27:25

The dates there shoud be OK but it onky takes ane to cause the error

Try this
create table #a (s varchar(8), t varchar(8))
insert #a select '03/13/41', '09/06/83'
insert #a select '04/16/52', '03/29/79'
insert #a select '09/07/50', '02/12/92'
insert #a select '04/30/62', '02/26/92'
insert #a select '04/11/61', '04/10/92'
insert #a select '06/25/54', '06/01/92'
insert #a select '03/27/65', '10/03/95'
insert #a select '12/02/56', '07/07/78'
insert #a select '10/14/48', '06/11/90'
insert #a select '01/18/42', '01/07/81'
insert #a select '04/07/59', '11/15/91'
select convert(datetime,s,1), convert(datetime,t,1) from #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-02 : 13:57:13

Thanks NR that work however, I alread have a user_temp table that I import data into. That is why the dates are varchar. Im trying to update the user table that has data in it. Here is what I have coded maybe you could tell me where I missed the boat
insert into appuser
(forename,
surname,
Job_Title,
Employment_Start,
DOB,
Sex,
Employee_Number,
Employment_End,
User_Type)

select
dbo.ProperCase(Forename)as Forename,
dbo.ProperCase(surname)as surname,
dbo.ProperCase(Job_Title)as job_title,
convert(datetime,Employment_Start,1), as Employment_Start
convert(datetime,DOB,1)as DOB,
sex,
ltrim(Employee_Number) as Employee_Number,
convert(datetime,Employment_end,1) as employment_end,
'e' as user_type
from Appuser_Temp

where ltrim(Employee_Number) not in
(select Employee_Number from appuser where Employee_Number is not null)

Thank God for Forums.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-02 : 14:17:10

assuming you don't really have a comma before as Employment_Start
try

set dateformat mdy
select * from Appuser_Temp
where isdate(Employment_Start) = 0 or isdate(DOB) = 0 or isdate(Employment_end) = 0
and ltrim(Employee_Number) not in
(select Employee_Number from appuser where Employee_Number is not null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vwilsonjr
Starting Member

45 Posts

Posted - 2003-04-02 : 15:59:11

Thanks NR

After you said it only took 1 to throw the error I looked through the data closely. Believe it or not the lovely people whose job it is to do data entry didn't know a correct date so they were just making numbers up or leaving them blank

Thanks again.

Thank God for Forums.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-02 : 22:34:54

Ahhhhh, yes, proof of the old saying:

Your data is only as good as the stupidest moron doing the data entry.

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-03 : 08:28:35

Is there anyway to override the error handling of SQL so that SELECT would generate a set of the rows with valid dates and omit the rows with invalid dates?

Sam

----------------------
The rule on staying alive as a forcaster is to give 'em a number or give 'em a date, but never give 'em both at once.
- Jane Bryant Quinn

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-03 : 08:38:07

quote:

Is there anyway to override the error handling of SQL so that SELECT would generate a set of the rows with valid dates and omit the rows with invalid dates?

What about ISDATE function..!!

SELECT Employeeid,BirthDate FROM northwind.dbo.employees
WHERE ISDATE(birthdate)=1

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 09:59:18

see my previous post - just swap the 0 to 1 and the or's to and's for the valid ones.
I think you need the set dateformat for it to work

set dateformat mdy
select * from Appuser_Temp
where isdate(Employment_Start) = 0 or isdate(DOB) = 0 or isdate(Employment_end) = 0
and ltrim(Employee_Number) not in
(select Employee_Number from appuser where Employee_Number is not null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-03 : 10:05:24

Sorry Nigel - didn't read your post carefully.

Sam

Edited by - SamC on 04/03/2003 10:06:38

RT_ran
Starting Member

2 Posts

Posted - 2012-02-01 : 04:53:55

I have a column in a table that has varchar dates in it "MM/DD/YY"
I need to convert it to a datetime.

I have tried convert(datetime,dte,1),convert(datetime,'31/12/2011',1) and get date overflow. I have looked and search and can't find anything that will fix it. I have also tried cast()

quote:
Originally posted by nr

if convert(datetime,dte,1) gives an error it probably means that one of the rows has an invalid date.
Before 1752 or whatever or too big?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RT_ran
Starting Member

2 Posts

Posted - 2012-02-01 : 04:53:57

quote:
Originally posted by nr

if convert(datetime,dte,1) gives an error it probably means that one of the rows has an invalid date.
Before 1752 or whatever or too big?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources