Converting a datetime field to month/year

[ashab02]

Hello

I have a view which currently has a datetime field in the following format: 01/07/2003 12:01:44

I would like just the month and year so I would like it to look like: 07/2003.

Alterantively I would like to basically filter on all data where it lies between last month. I have tried using '>MONTH(GETDATE())-1' but this brings back random dates back.

Can anyone show me a way of capturing data from the previous month. This needs to be automated so a BETWEEN stateement is no good to me.

Can anyone help?????

 

[bborissov]

SELECT RIGHT(CONVERT(varchar(10),YourDTField,103),7) AS Something
FROM YourTable
WHERE YourDTField > DateAdd(mm,-1,GETDATE())

BUT Becarefull, GETDATE() returns FULL Date AND Time. That means you If you call this query at: 02/10/2006 14:30:25 h you will miss all records that are smaller or equal to
02/10/2006 14:30:24

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

SELECT RIGHT(CONVERT(varchar(10),YourDTField,103),7) AS Something
FROM YourTable
WHERE YourDTField > DateAdd(mm,-1,GETDATE())

BUT Be carefull, GETDATE() returns FULL Date AND Time. That means you If you call this query at: 02/10/2006 14:30:25 h you will miss all records that are smaller or equal to
02/10/2006 14:30:24

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP