Solved

convert varchar -> datetime with <nulls> and dd/mm/yyyy 00:00:00 in field [out-of-range error]

Posted on 2004-10-01
17
347 Views
Last Modified: 2008-03-04
Hi,

I have 40,000 records which I want to to convert the date of birth field.

the field has about 50% of <NULL> and the rest have dates.

The dates are in the format for example 04/05/1977 0:00:00

I can remove the 0:00:00 because it is not relevant. (Is there an SQL command I can use to remove all of these?)

Is the NULL or the time causing the out-of-range error and is there any way around this?

I need to convert it to add more date records and dates not varchars in the future.

Regards,
tp
0
Comment
Question by:tippingpoint
  • 8
  • 8
17 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12205971
If you want to strip the 0:00:00 out of an existing varchar column this will do it:

UPDATE TableA SET ColA = Replace(ColA, ' 0:00:00','')

If you have date values in a varchar column and convert the column to a datetime data type, it will be stored as a number. If no time is specified the number will always be equal to 12 AM of the date.

If you want us to help you figure out what's causing the error, you need to post the query/command you're executing to generate the error.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12205983
You need to look at the nulls before you attemp the conversion.  If your varchar "date" is always in MM/DD/YYYY then this will select the dates using left (you can build an update statement out of it as well):


SELECT CASE WHEN yourdobfield IS NULL THEN NULL
       ELSE CONVERT(DATETIME,LEFT(yourdobfield,10)) END
FROM yourinputtable
0
 

Author Comment

by:tippingpoint
ID: 12206159
arbert:
This is the error I got.

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tippingpoint
ID: 12206177
jdlambert1:
I have removed the times but it seems the <NULLS> in that column are not allowing me to convert it to datetime

Any other suggestions?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12206178
So I take it you must also have some invalid dates in the column???

select
case when isdate(left(yourdobfield,10)) then CONVERT(DATETIME,LEFT(yourdobfield,10))
else null END
from yourinputtable
0
 
LVL 34

Expert Comment

by:arbert
ID: 12206182
Actually, sorry I didn't ask this earlier, what's the existing format of the field that contains DOB?  If there happen to be spaces at the beginning of the string, the would need to be stripped off with LTRIM.
0
 

Author Comment

by:tippingpoint
ID: 12206183
arbert:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'then'.

still no good, do you think there are bad entries in there?
0
 

Author Comment

by:tippingpoint
ID: 12206192
it was a varchar,

I have removed spaces
0
 
LVL 34

Expert Comment

by:arbert
ID: 12206194
Sorry--I tested the first one and changed it without testing the second:

select case isdate(dobfield)
        when 1 then  CONVERT(DATETIME,LEFT(dobfield,10))
            else null END
from yourtable
0
 

Author Comment

by:tippingpoint
ID: 12206197
If there are invalid date entries in there is there a way to check if they are invalid and make them null??

0
 
LVL 34

Expert Comment

by:arbert
ID: 12206201
That's what the above does--if ISDATE is 0 then the date is invalid and it will return NULL.  If it's 1, it performs the conversion.
0
 

Author Comment

by:tippingpoint
ID: 12206209
It seems as though the above works, but it is converting my dates wrong using LEFT

my format is DD/MM/YYYY

it is being converted to YYYY-MM-DD which is ok but it is swicthing the DD and MM around in the output

0
 
LVL 34

Expert Comment

by:arbert
ID: 12206220
Sorry--dumb american here :)

add a 103 to the convert:

select case isdate(dob)
when 1 then  CONVERT(DATETIME,LEFT(dob,10),103)
else null END
from #datetest


The YYYY-MM-DD returned is just the iso representation of the date....
0
 

Author Comment

by:tippingpoint
ID: 12206233
ok all good!! :)

last thing..how do i update into that data table with that query... sorry i fairly new to sql!
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 12206250
Well, I would either add a new column with a smalldate time definition, or you could update the existing data in-place and then change the column definition afterwards.

Update existing:

update your table
set dob= case isdate(dob)
when 1 then  CONVERT(DATETIME,LEFT(dob,10),103)
else null END


If you add a new column, just substitute the "set dob=" to be "set younewcolumn="

Brett


0
 
LVL 34

Expert Comment

by:arbert
ID: 12206296
Did you get it tp?
0
 

Author Comment

by:tippingpoint
ID: 12206347
VERY GOOD..

THANK YOU SO MUCH.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query question 12 32
listing SQL login names of valid databases 2 20
MS SQL query to show nearest date 6 32
SQL Availablity Groups Shared Path 2 13
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question