Newbie needs help with Date view

[weblead]

Hi,

I am new to SQL 2000 other than writing simple statements for asp page recordsets, delete, update etc.

I have just ventured into my first view to solve a problem.... need some help please.

I have inherited a DB where the date info for meetings have been entered in 3 seperate fields StartYear, StartMonth, StartDay. This wasn't a problem to display data and sort etc. But now I have been asked to have a 'rolling' recordset display, i.e. showing 1 month previously and all future meetings.

I have done this before using > (now() -30) in the SQL when the date was in one field 2004-06-15.

I know I need to concatenate. So I made a view (view_StartDate) using "SELECT StartYear + '-' + StartMonth + '-' + StartDay AS StartDate, dbo.tbl_Meetings.*
FROM dbo.tbl_Meetings"
This gives me 2004-07-22....fine in theory but....

On the asp page I use "SELECT * FROM dbo.view_StartDate WHERE StartDate > '(now() -30)'
ORDER BY StartDate"

This returns all meetings. I have figured out that (now() -30) only works with datetime fields. The question is how do you specify the view field StartDate's properties as datetime.

Or am I barking completely up the wrong tree?

Any help appreciated,

Jenna.

 

[rac2]

Convert the string, StartDate to a DATETIME value.

CREATE VIEW view_StartDate AS
SELECT CAST( StartYear + '-' + StartMonth + '-' + StartDay AS DATETIME) AS StartDate, dbo.tbl_Meetings.*
FROM dbo.tbl_Meetings

 

[ESquared]

Just FYI, look up the SET DATEFORMAT command so you'll be familiar with it if you ever have problems with month and day getting switched.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[weblead]

Fabulous! Thanks all.