Hi,
I'm using SQL 2000, Query Analyser.
I have a table 'dbo.progress_movhdr_NW.' that contains a Date field [Movement Date-New] format is 'dd/mm/yyyy' and a Year field [MAT Year] format is 'yyyy'. I need to populate/update the Year field, depending on the value held in the Date field (the Year field only contians Nulls at present).
[Movement Date-New] is datetime
[MAT Year] is varchar
The following code doesn't work. I'm obviously doing something wrong!!
Code:
UPDATE dbo.progress_movhdr_NW
SET dbo.progress_movhdr_NW.[MAT Year] =
CASE
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '01/01/1993' AND '17/09/1994' THEN '1994'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '18/09/1994' AND '16/09/1995' THEN '1995'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '17/09/1995' AND '13/09/1996' THEN '1996'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '14/09/1996' AND '13/09/1997' THEN '1997'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '14/09/1997' AND '12/09/1998' THEN '1998'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '13/09/1998' AND '18/09/1999' THEN '1999'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '19/09/1999' AND '16/09/2000' THEN '2000'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '17/09/2000' AND '15/09/2001' THEN '2001'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '16/09/2001' AND '14/09/2002' THEN '2002'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '15/09/2002' AND '13/09/2003' THEN '2003'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '14/09/2003' AND '18/09/2004' THEN '2004'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '19/09/2004' AND '17/09/2005' THEN '2005'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '18/09/2005' AND '16/09/2006' THEN '2006'
WHEN dbo.progress_movhdr_NW.[Movement Date-New]
BETWEEN '17/09/2006' AND '15/09/2007' THEN '2007'
ELSE '????'
END
Can anyone help, please?
Thanks in advance.
Neal
A Northern Soul
Update query problem
