SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Convert varchar to date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vwilsonjr
Starting Member

45 Posts

Posted - 04/02/2003 :  12:19:14  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/02/2003 :  12:41:49  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 04/02/2003 :  12:49:49  Show Profile  Reply with Quote
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 - 04/02/2003 :  13:06:16  Show Profile  Reply with Quote
use select convert(datetime,'3/13/41',1)

this works fine!

Go to Top of Page

vwilsonjr
Starting Member

45 Posts

Posted - 04/02/2003 :  13:17:50  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/02/2003 :  13:27:25  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 04/02/2003 :  13:57:13  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/02/2003 :  14:17:10  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 04/02/2003 :  15:59:11  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 04/02/2003 :  22:34:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
3464 Posts

Posted - 04/03/2003 :  08:28:35  Show Profile  Reply with 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?

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

India
437 Posts

Posted - 04/03/2003 :  08:38:07  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 04/03/2003 :  09:59:18  Show Profile  Visit nr's Homepage  Reply with Quote
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

USA
3464 Posts

Posted - 04/03/2003 :  10:05:24  Show Profile  Reply with Quote
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 - 02/01/2012 :  04:53:55  Show Profile  Reply with Quote
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 - 02/01/2012 :  04:53:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000