Problem converting a date from a text field

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)

Problem converting a date from a text field

Author

Topic

zigger1_2000
Starting Member

5 Posts

Posted - 2004-01-29 : 20:30:20

I have a field with the following date format mmdddyy which is a varchar field
e.g. 30jan02

This is stored in a text field. However it is not very searchable using the front end application which is a document management system.

So i though i'd get the correct datetime format dates by doing the following:-

select cast (notes1 as datetime) from tmp_edms_attributes

notes1 is the text field with the dates in.

However users have been putting bad data into the table

e.g. 30-jan-04 or 30jan 04 etc.

Therefore when the select query gets so far down the table I get the following error message:-

(43 row(s) affected)

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

Can anyone give me some advice.

I am really wanting to make a new datetime field and populate this with NULL or the correct UK datetime e.g 30/01/04

Help with a script to do this would be grately appreciated.

Cheers

Chris
email:- Zigger1_2000@hotmail.com
website:- http://www.astralforum.com

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-30 : 04:09:51

check (before converting) does a notes1 value look like a valid date:

select notes1,
case when isdate(notes1)=1 then cast(notes1 as datetime) end
from t

then all "dates" from the 1st column with nulls from the 2nd column
should be corrected "manually".

zigger1_2000
Starting Member

5 Posts

Posted - 2004-01-30 : 05:46:20

Thanks. I will try this tonight and let you know how I got on.

Cheers

Chris

mr_mist
Grunnio

1870 Posts

Posted - 2004-01-30 : 06:13:35

Remember that really the problem here is with your front end application allowing people to enter the bad data. Unless you want to do continual ad-hoc firefighting you're better off sorting that out.

-------
Moo. :)

zigger1_2000
Starting Member

5 Posts

Posted - 2004-02-04 : 05:04:25

Stoad,

Thanks the script works great.

Now what do I need to append to the script if I have a new field in the table that will hold the datetime, which i need populating.

e.g. go through each row and populate the notes1 field with the correct format using the case command as you showed me into the new datetime field.

Also if I want a database level trigger that will correctly input the datetime from notes1 into the new field when a row is inserted or updated? (This will then cover the system for new data from users)

Sorry to ask so much but I am still learning and can only do basic sql update, insert and select statements for now.

Cheers

Chris
--------------------------------------------------
mr_mist,

I am dealing with an application that I do not have the sourcecode to so can't constrain user input from the application frontend, the developers are far too busy to even respond :(

Cheers

Chris

zigger1_2000
Starting Member

5 Posts

Posted - 2004-02-04 : 08:02:51

I sorted the update command out:-

update dbo.tmp_EDMS_Attributes set newdate =
case when isdate(notes1)=1 then cast(notes1 as datetime) end
from dbo.tmp_EDMS_Attributes

Just need a trigger now to keep all data in the newdate field upto date with a trigger when a new row is inserted or a row is updated in the tmp_EDMS_Attributes table

Any help appreciated.

Cheers

Chris

zigger1_2000
Starting Member

5 Posts

Posted - 2004-02-04 : 09:09:10

All,

Whilst I was waiting for a reply I thought I would sit down and try to work this out using the MSSQL books online. After generating myself a headache I came up with this:-

CREATE TRIGGER update_notesdt
ON tmp_edms_attributes
FOR INSERT, UPDATE
AS

declare @projno int,
@itemno int
select @projno = o_projectno, @itemno = o_itemno from inserted

update dbo.tmp_EDMS_Attributes set notes1dt =
case when isdate(notes1)=1 then cast(notes1 as datetime) end
where o_projectno = @projno and o_itemno = @itemno

GO

It seems to work.
However if anyone can see a performance issues with the above or suggest a better way of handling this then let me know.

Cheers

Chris

MuadDBA

628 Posts

Posted - 2004-02-04 : 10:20:06

since you are executing a select inside your trigger, you will want to SET NOCOUNT DISPLAY ON so the application doesn't have to try to deal with a returned value when this trigger executes.

Also your trigger isn't really filtering out the bad data. When a user tries to submit bad data, your trigger should do a RAISERROR (hopefully your application is smart enough to handle that) and let the user know they entered a bad date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources