Date evaluation

Neal · May 16th, 2007, 03:35 AM

Hi,

I'm trying to test for date values in a varchar field. Unfortunately this is inherited in this manner and
I need to run a query based on this field (MAT_Date) and a 'YYYY' field MAT_Year to populate
a third field (MAT_Period) in the same table with (4 week) period values.

[u]Field properties</u>
MAT YEAR = varchar (255)
MAT_DATE = varchar (50)
MAT_Period = varchar (50)

I thought of using the following code, but there are some Null values where in the resulting MAT_Period column.

(i.e
Where
MAT YEAR = 2003
MAT_DATE = 29/09/2002

MAT_Period = Null

AND

Where
MAT YEAR = 2003
MAT_DATE = 29/09/2002

MAT_Period = Null
)

Code:

use crm_marketing
go
UPDATE dbo.progress_movhdrmat_date
SET [MAT_Period] =
--Year 2003
CASE WHEN dbo.progress_movhdrmat_date.[MAT Year] = '2003' AND 
dbo.progress_movhdrmat_date.[MAT_Date]  >= '15/09/2002' AND
dbo.progress_movhdrmat_date.[MAT_Date]  <= '12/10/2002'
THEN '01' 
WHEN dbo.progress_movhdrmat_date.[MAT Year] = '2003' AND 
dbo.progress_movhdrmat_date.[MAT_Date]  >= '13/10/2002' AND
dbo.progress_movhdrmat_date.[MAT_Date]  <= '09/11/2002'
THEN '02'
END
FROM dbo.progress_movhdrmat_date

I then tried to format(Mask) the MAT_Date field as a DATE type:

Code:

use crm_marketing
go
UPDATE dbo.progress_movhdrmat_date
SET [MAT_Period] =
--Year 2007
WHEN dbo.progress_movhdrmat_date.[MAT Year] = '2003' AND 
CAST(dbo.progress_movhdrmat_date.[MAT_Date] AS SMALLDATETIME) >= '15/09/2002' AND
CAST(dbo.progress_movhdrmat_date.[MAT_Date] AS SMALLDATETIME) <= '12/10/2002'
THEN '01'
WHEN dbo.progress_movhdrmat_date.[MAT Year] = '2003' AND 
CAST(dbo.progress_movhdrmat_date.[MAT_Date] AS SMALLDATETIME) >= '13/10/2002' AND
CAST(dbo.progress_movhdrmat_date.[MAT_Date] AS SMALLDATETIME) <= '09/11/2002'
THEN '02'
END
FROM dbo.progress_movhdrmat_date

But got the following error message displayed:

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

Any ideas please?

Thanks in advance,

Neal

A Northern Soul

happygv · May 16th, 2007, 04:50 AM

Neal,

It is very simple when you have them as DATETIME or SMALLDATETIME. I dont understand why you have kept them as VARCHAR, when SQL supports DATETIME datatype to deal with date values and exclusive built-in functions for Datetime datatype. Thus you are making your life tough dealing with dates that are or varchar types.

Quote:

quote:CAST(dbo.progress_movhdrmat_date.[MAT_Date] AS SMALLDATETIME) >= '15/09/2002' AND
CAST(dbo.progress_movhdrmat_date.[MAT_Date] AS SMALLDATETIME) <= '12/10/2002'

Even if this works fine after fixing your values right, you CANNOT compare a date value falling within a date range as you expect it to work, since those are considered as simple texts, not as date values at present.

Also You cannot check for NULL values using = operator. Use IS keyword for such checks.

WHERE COLUMN_NAME IS NULL

Hope that explains.
Cheers.

_________________________
- Vijay G
Strive for Perfection

Neal · May 16th, 2007, 05:10 AM

Hi Vijay,

1. The table is imported (automatically) with these values by DTS. I'm trying to generate SQL queries that are automated and can read/evaluate these values.

2. The section that I described previously....
(i.e
Where
MAT YEAR = 2003
MAT_DATE = 29/09/2002

MAT_Period = Null

AND

Where
MAT YEAR = 2003
MAT_DATE = 29/09/2002

MAT_Period = Null
)

these are examples of results I'm getting.
To clarify, in some of the updated records, if the value of MAT YEAR is '2003' and MAT_DATE is '29/09/2002' then the result, for some reason the field MAT_Period = Null.

I appreciate your comments though!

Neal

A Northern Soul

happygv · May 16th, 2007, 05:28 AM

Okay, I misunderstood it that you are checking for NULL that way. Didn't know it to be the result :)

Quote:

quote:The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.

On this... the date value that you pass is DD/MM/YYYY, which is why it gives you that error. Is it by anychance can you get it as mm/dd/yyyy or yyyy/mm/dd in your DTS operation? That should solve it.

_________________________
- Vijay G
Strive for Perfection

happygv · May 16th, 2007, 05:30 AM

I got that... To fix that you should issue this statement before your UPDATE...

Code:

SET DATEFORMAT dmy
--The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

This tells the sql server that the datevalue that you passed for this particular batch is of DD/MM/YYYY format.

_________________________
- Vijay G
Strive for Perfection

Neal · May 16th, 2007, 07:21 AM

Thanks Vijay,

I'll give it a try.

Neal

A Northern Soul