Pre-validation of date before conversion within a script 1

[sewilj]

I have the following query that I have written for a database running on SQL 7.

The query relies on the users inserting a specific string into a memo field within the application which is stored in a CHAR field in the database. What they enter is

LRD dd/mm/yyyy

unfortunately this information can not be entered into any other date specific field, so I have to extract the date and convert to make it useable. (lrd also has to be entered)

The problem lies in that if the users input the date in the wrong format, such as

lrd 17/5/06
lrd 07-05-2006 etc...

the conversion will not take place.

What I would like to be able to do is to find away to pre-validate the date format before trying to convert the data within the script?

I know what I want, i'm just not sure how to get it?

Could anybody offer me an answer or at least a gently knudge in the right direction! ;-)

Thanks

Lewis
(Please see below for the script)

select m.serno, m.date as 'Memo_Date', convert(datetime,substring(m.line_mess,5, 10),103) as 'LRD_Date',
m.inits as 'Memo_inits',h.seq, h.state, P.DESC1,
h.date as 'History_date', h.inits as 'History_inits', h.letter_code, h.type, C.STATE AS 'PCN State', PP.DESC1 AS 'PCN DESC'
from cpmemo m
join cphist h on h.serno = m.serno
JOIN CPPROG P ON P.CODE = H.STATE AND P.TYPE = H.TYPE
JOIN CPCASE C ON C.SERNO = M.SERNO
JOIN CPPROG PP ON PP.CODE = C.STATE AND PP.TYPE = C.TYPE
where left(line_mess,3) = 'lrd' and convert(datetime,substring(m.line_mess,5, 10),103) between Startdate and Enddate
order by m.serno, h.seq

Lewis
United Kingdom

 

[gmmastros]

The 2 formats you show should convert to a proper datetime field. Could it be other 'dates' that are not really dates?

Try running this query to see if there is anything buggy about your data.

SET DATEFORMAT DMY

Select * 
From   cpmemo 
Where  Left(line_mess, 3) = 'lrd'
       and IsDate(substring(line_mess, 5, 10)) = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

This validation REALLY should be done by the application. However, if it has to be done in SQL Server, you might try using a trigger and validating it there.

I think I would have the trigger strip the first four characters (LRD and a space) and then determine if the date is valid. Now there's the catch. Is 1/2/2006 - Jan 2, 2006 or is it Feb 1, 2006? How will you know which one the user meant? If it's always for the current month, you could use GETDATE() to do a comparison.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

George,

I think they were 'wrong' formats because they are going in a CHAR field (not DATETIME0 and the first has a two digit year (should be four digits) and the second has dashes instead of slants.

Why are dates being stored as strings vice DATETIME? The poster doesn't say.....it's not the best way, but how many times has this been seen? Someone or some application always seems to store dates as CHAR/VARCHAR/NCHAR/NVARCHAR.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Bill,

I agree. It is ALWAYS a mistake to store more than 1 piece of data in a field. In this case, whatever LRD represents, should be stored in its own field, and the date should be stored in a datetime field.

I also recognize how this sort of thing can happen though. Even here, we had a contact management app that people were using the memo field to store various bits of information. Later, they want to search on those fields and instantly the problem becomes apparent. It's not an eay situation to accomodate. Hopefully, the OP will notice the use of the SET DATEFORMAT command, which tells SQL Server that dates are in day/month/year format, and my use of the IsDate function which returns a 1 for valid dates and a 0 for invalid dates.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sewilj]

Thank for you comments so fast.

To explain why we have LRD and the date added to the CHAR field. We need to record the date which is stamped onto an envelope when correspondance is sent into our office. We then scan the item to our database. We do not have the ability to enter the date into a specific date field without having our core application amended - something that will be both costly and length to get done. We also use EDS, which even though is for exactly the same information just helps to identify which team within the department is handling what correspondance.

In order to answer a few other questions -

1) If I could get the app to validate the date then I would but unfortunately its not that easy (see above).
2) Our staff have been informed that they need to enter the info as either LRD or EDS DD/MM/YYYY, so wither

LRD 17/05/2006 oe EDS 17/05/2006

It just appears to be the case that some of them are incapable of doing this...


Can the set dateformat just be set when running single queries, or once set, is it set for the entire database?

Hope this further info helps to explain why we are doing what we are!

Thanks

Lewis


Lewis
United Kingdom

 

[gmmastros]

set dateformat DMY only applies to the currently running query. Typically, you would include this line at the top of a stored procedure that you use to select the data you want.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sewilj]

Just to let you know that I am currently trying to sort it using the set dateformat and isdate functions.

For info the query that I posted is part of a stored procedure which is used by used by a crystal report to extract management info on performance. Full SP below

CREATE PROCEDURE [DBO].LRD_Date
@LRD_mindate datetime,
@LRD_maxdate datetime
as
declare
@BeginDate DATETIME,
@EndDate DATETIME

/* "Chop off" the times passed from Crystal */
SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_mindate, 101))
SELECT @EndDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_maxdate, 101))
/* go the last second on the last date */
SELECT @EndDate = DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate))

select m.serno, m.date as 'Memo_Date', convert(datetime,substring(m.line_mess,5, 10),103) as 'LRD_Date',
m.inits as 'Memo_inits',h.seq, h.state, P.DESC1,
h.date as 'History_date', h.inits as 'History_inits', h.letter_code, h.type, C.STATE AS 'PCN State', PP.DESC1 AS 'PCN DESC'
from cpmemo m
join cphist h on h.serno = m.serno
JOIN CPPROG P ON P.CODE = H.STATE AND P.TYPE = H.TYPE
JOIN CPCASE C ON C.SERNO = M.SERNO
JOIN CPPROG PP ON PP.CODE = C.STATE AND PP.TYPE = C.TYPE
where left(line_mess,3) = 'lrd' and convert(datetime,substring(m.line_mess,5, 10),103) between @Begindate and @Enddate
order by m.serno, h.seq
GO


Lewis
United Kingdom

 

[gmmastros]

Try this...

Create PROCEDURE [DBO].LRD_Date 
@LRD_mindate datetime,
@LRD_maxdate datetime
as

[!]SET NOCOUNT ON
SET DATEFORMAT DMY[/!]

declare @BeginDate DATETIME
declare @EndDate DATETIME

/* "Chop off" the times passed from Crystal */
SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_mindate, 101))
SELECT @EndDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_maxdate, 101))
/* go the last second on the last date */
SELECT @EndDate = DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate))

select m.serno, 
       m.date as 'Memo_Date', 
       convert(datetime,substring(m.line_mess,5, 10),103) as 'LRD_Date', 
       m.inits as 'Memo_inits',
       h.seq, 
       h.state, 
       P.DESC1,
       h.date as 'History_date', 
       h.inits as 'History_inits', 
       h.letter_code, 
       h.type, 
       C.STATE AS 'PCN State', 
       PP.DESC1 AS 'PCN DESC'
from   cpmemo m
       join cphist h on h.serno = m.serno
       JOIN CPPROG P ON P.CODE = H.STATE AND P.TYPE = H.TYPE
       JOIN CPCASE C ON C.SERNO = M.SERNO
       JOIN CPPROG PP ON PP.CODE = C.STATE AND PP.TYPE = C.TYPE
where  left(line_mess,3) = 'lrd' 
       and [!]Case When IsDate(substring(m.line_mess,5, 10)) = 1
                Then convert(datetime,substring(m.line_mess,5, 10),103)
                Else @BeginDate - 1
                End between @Begindate and @Enddate[/!]
order by m.serno, h.seq
GO

The Case/when/then business basically states that is we have a valid date, then use it, otherwise, use @BeginDate - 1, so that it will fall outside the range and not be returned.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sewilj]

George
thanks for the response! i'll give it a try when i'm in the office tomorrow morning.
thanks

Lewis
United Kingdom

 

[gmmastros]

You're welcome.

Please post back after trying this. I'm anxious to know if this was helpful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sewilj]

George

This was very useful. With a little tinkering with the 2 lines below - changing the 101 to 103 to works.

SELECT @BeginDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_mindate, 101))
SELECT @EndDate = CONVERT(DATETIME, CONVERT(CHAR(10),@LRD_maxdate, 101))

Many many thanks for your help in resolving this, Saved me a lot of time and frustration!!!

Thanks

Lewis
United Kingdom

 

[sewilj]

George

Any chance you could just explain the case logic a little bit. I understand about using case logic, the bit I am unsure about is setting @begindate -1. I would think that this would just reduce the @begindate by one day and therefore increase the query range by one day. Obviously it does not and it ignores those with an incorrect date, but I am not sure how it does it.

Case When IsDate(substring(m.line_mess,5, 10)) = 1
Then convert(datetime,substring(m.line_mess,5, 10),103)
Else @BeginDate - 1
End between @Begindate and @Enddate

Could you explain a bit further?

Thanks


Lewis
United Kingdom

 

[gmmastros]

The between is the operator here. We want to make sure we are comparing apples to apples, meaning, we want a date on both sides of the operator. If the date is not valid, you want to ignore it, so, to ignore it, we can simply set it outside the acceptable range (by subtracting one day from the lower bound), and that record will get filtered out.

When you subtract 1 from a date, you get the previous date:

Select GetDate() As Today, GetDate() - 1 As Yesterday

Using this same logic, you could also have used Else @EndDate + 1

I hope this clrearly explains the logic. If not, post back and I will explain further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sewilj]

George


The only bit I am still unsure about is the @begindate - 1

I'll try to put into words why it still confuses me

As the @begindate and @enddate variables are input externally from a crystal report, and are set for example as
@begindate = 01/01/2006
@enddate = 31/01/2006

why then, when the case logic does "Else @BeginDate - 1",
does this not just change the date range to

@begindate = 31/12/2005
@enddate = 31/01/2006

but rather excludes does not return the data?

Apologise if I seem a bit thick, its just I would rather know how it works rather than know that it "just does"

cheers


Lewis
United Kingdom

 

[gmmastros]

@BeginDate - 1 is not modifying the begin date, it's returning a value that is used to compare the date range with. Let me try to explain with an example.

Declare @Temp Table(TheDate VarChar(100))

Insert into @Temp Values('2006-05-18')
Insert into @Temp Values('2006-02-18')
Insert into @Temp Values('2006-03-18')
Insert into @Temp Values('2006-04-18')
Insert into @Temp Values('Yesterday')

Select TheDate,
       Case When IsDate(TheDate) = 1
            Then Convert(DateTime, TheDate)
            Else '1990-01-01'
            End As DateToCompareWith
From   @Temp

In this example, 'yesterday' is not a valid date, so instead of converting the varchar to a datetime, we return '1990-01-01'. Therefore, the CASE part of the statement is ALWAYS returning a date, even if the original data had an invalid date.

When you run this is Query Analyzer, you'll see a 'TheDate' column that represents the original data. You'll also see a 'DateToCompareWith' column. In the row that has 'Yesterday' as the original data, you'll see that 'DateToCompareWith' shows 1990-01-01.

Now imagine we wanted to filter records from this table so that only records from 2006 are returned. The 'DateToCompareWith' for the invalid date is jan 1 1990, so this record wouldn't be returned.

Does this help?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should state that you could probably have used a hardcoded value, like '1900-01-01' instead of @BeginDate - 1.

Since I don't know what your application does, I couldn't possibly guess what a good 'hardcoded' value would have been. You could be tracking historical data, where jan 1 1900 is a perfectly acceptable date within the table. By using @BeginDate - 1, you are quaranteed that invalid dates will fall outside the range of dates that you are reporting on.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sewilj]

George

Now it makes sense. I think the problem lied in that I though by the case clause was reducing the @begindate by 1. Now I understand that if the date was invalid the case logic set it to B rather than A before then comparing it with the date range.

Again many thanks for the help

Lewis
United Kingdom