Errors converting VarChar to smalldatetime

[hpadwal]

Hello all,

i get the following error

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

When i execute

Update tbl_users
Set user_dob = convert(smalldatetime, user_dob, 103)
Where user_dob is not null

I am trying to convert DD/MM/YYYY to smalldatetime

can you help





_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hneal98]

is user_dob a varchar?

 

[SQLBill]

Use this command at the beginning of your script.

SET DATEFORMAT DMY

-SQLBill

Posting advice: FAQ481-4875

 

[hpadwal]

yep, user_dob is varchar at the moment

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hpadwal]

no, unfortuanatly i still get the same error with

SET DATEFORMAT DMY

Update tbl_users
Set user_dob = CAST(user_dob AS smalldatetime)

Where user_dob is not null

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[vongrunt]

Run this:

select * 
from tbl_users
where user_dob is not null
	and isdate (user_dob) = 1
	and convert(datetime, user_dob) not between '19000101' and '20790606'

?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[hpadwal]

thanks for the input guys, but getting a different message now

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.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hpadwal]

that error appears wen i use vongrunt's example

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hneal98]

Try this instead...

Update tbl_users
Set user_dob = convert(smalldatetime, convert(smalldatetime,user_dob), 103)
Where user_dob is not null

You have Where user_dob is not null

 

[SQLBill]

1. You have dates that are Feb 29 for non-leap years
or
2. you have values that have the month first, day second.

-SQLBill

Posting advice: FAQ481-4875

 

[hpadwal]

yes! my first 3200 entries are MMDDYYY i didnt mention this earlier because insert the extra bits on my side

i use

Update tbl_users
Set user_dob = convert(smalldatetime, convert(smalldatetime,user_dob), 101)
where user_id < 3273

i get

Syntax error converting the varchar value 'lla' to a column of data type int.

i think i see my hair falling out?!!?

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[vongrunt]

Easy... :>

Looks like user_id is Yet Another Varchar Column (tm). True?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[hneal98]

That is a problem with your data. :-(

 

[hpadwal]

Ouch, sorry thank for pointing that out it is supposed to be

id < 3273

user_id is a varchar column! I corrected that but now i am back to my original error of

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I tihink i will start to work on an asp script that will convert these values instead
:s


_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hneal98]

It is intersting that you getting that error. Your update query above was tested and returned the same results as you said, but when I made the changes that I suggested above, it worked properly.

If you are still getting this error, I am thinking there must be problems in your data.

 

[hpadwal]

Hey guys thanks for all your input,

Just for other users who may have the same problem, here is the solution i finally used.

<%
session.lcid = 2057

' connection code here


Set rsUpdate = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tbl_users"
rsUpdate.CursorType = 2
rsUpdate.LockType = 3

rsUpdate.Open strSQL, db

	do while not rsUpdate.EOF

		oldStr = rsUpdate("user_dob")
		new_date = formatdatetime(oldStr)

			rsUpdate.Fields("new_dob") = new_date
			
			rsUpdate.Update
		
	rsUpdate.movenext: loop

rsUpdate.Close
Set rsUpdate = Nothing
Set DB = Nothing

%>

thanks again

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[hneal98]

I thought this started out a SQL problem? You came to a SQL forum for a VB solution?

 

[vongrunt]

Makes sense, if data is really dirty.

I'm still curious what would that code update for out-of-range user_dob values...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[hpadwal]

arrrr yes thats the catch, us dates that had the middle value XX/thisone/XXXX under 12 can not be changed, so although all the date now appear to be UK dates, there are still dates which are US style as the middle value was less then 12!

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk