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.
Go to Top of Page

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.
Go to Top of Page

mcp111
Starting Member

44 Posts

Posted - 2003-04-02 : 13:06:16
use select convert(datetime,'3/13/41',1)

this works fine!

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page

RT_ran
Starting Member

2 Posts

Posted - 2012-02-01 : 04:53:57
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.

Go to Top of Page
   

- Advertisement -