| Author |
 Topic  |
|
|
vwilsonjr
Starting Member
45 Posts |
 Posted - 04/02/2003 : 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
United Kingdom
12543 Posts |
Posted - 04/02/2003 : 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 - 04/02/2003 : 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
40 Posts |
Posted - 04/02/2003 : 13:06:16
|
use select convert(datetime,'3/13/41',1)
this works fine!
|
|
|
|
vwilsonjr
Starting Member
45 Posts |
Posted - 04/02/2003 : 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
United Kingdom
12543 Posts |
Posted - 04/02/2003 : 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 - 04/02/2003 : 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
United Kingdom
12543 Posts |
Posted - 04/02/2003 : 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 - 04/02/2003 : 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
USA
15559 Posts |
Posted - 04/02/2003 : 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
USA
3459 Posts |
Posted - 04/03/2003 : 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
India
437 Posts |
Posted - 04/03/2003 : 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
United Kingdom
12543 Posts |
Posted - 04/03/2003 : 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
USA
3459 Posts |
Posted - 04/03/2003 : 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 - 02/01/2012 : 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 - 02/01/2012 : 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.
|
|
|
| |
Topic |
|
Convert varchar to date
