Get records from previous month or older

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get records from previous month or older

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2007-12-22 : 18:09:50

Hi,

I have journey dates that are stored in my table as eg. 22/10/2007, 17/12/2007 etc in Datetime format.

I need to retrieve records that have a journey date of any day in the previous month or older. I am currently using this:

set dateformat dmy
set nocount on
SELECT	b.BookingId, b.JourneyDate, b.JourneyTime, cd.Account_Name, cd.Account_Contact, 
		LTRIM(STR(b.Total-b.TotalVat,8,2)) [NET],
		LTRIM(STR(b.TotalVat,8,2)) [VAT], LTRIM(STR(b.Total,8,2)) [TOTAL]
FROM		dbo.Bookings b with (nolock) INNER JOIN dbo.Customer_Details cd with (nolock)
ON		b.CustomerId = cd.CustomerId
WHERE	cd.ProfileType = 'Account'
AND		b.PaymentType = 'Account'
AND		IsValidated = 0
AND		IsExported = 0
AND		BookingStatus = 'COMPLETED'
AND 		(DATEPART(Month,convert(varchar,b.JourneyDate,103)) <= DATEPART(Month,convert(varchar,DateAdd(month,-1,getdate()),103)))
ORDER BY	b.JourneyDate, b.JourneyTime
set nocount off
GO

This works ok at the moment when the DATEPART function returns a high number but when we enter January in a few days this will no longer work.

Can anyone help me find a way to consistently return the records where the journeydate is for the previous month or older please.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-23 : 02:02:03

[code]set dateformat dmy
set nocount on
SELECT b.BookingId, b.JourneyDate, b.JourneyTime, cd.Account_Name, cd.Account_Contact,
LTRIM(STR(b.Total-b.TotalVat,8,2)) [NET],
LTRIM(STR(b.TotalVat,8,2)) [VAT], LTRIM(STR(b.Total,8,2)) [TOTAL]
FROM dbo.Bookings b with (nolock) INNER JOIN dbo.Customer_Details cd with (nolock)
ON b.CustomerId = cd.CustomerId
WHERE cd.ProfileType = 'Account'
AND b.PaymentType = 'Account'
AND IsValidated = 0
AND IsExported = 0
AND BookingStatus = 'COMPLETED'
AND b.JourneyDate < CONVERT(datetime,'01/'+ CASE
WHEN MONTH(GETDATE())<10 THEN '0' + MONTH(GETDATE())
ELSE MONTH(GETDATE())
END + YEAR(GETDATE()))
ORDER BY b.JourneyDate, b.JourneyTime
set nocount off
GO[/code]

patshaw
Posting Yak Master

177 Posts

Posted - 2007-12-23 : 06:49:51

Hi, thanks for reply but I now get:

"Syntax error converting the varchar value '01/' to a column of data type int."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-23 : 06:56:41

don't convert to string and compare. Compare datetime with datetime

and b.JourneyDate < select dateadd(month, datediff(month, 0, getdate()), 0)

KH
[spoiler]Time is always against us[/spoiler]

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-25 : 17:46:22

What about
[CODE]
DECLARE @thisMonth char(2)
DECLARE @thisYear char(4)
DECLARE @firstDOM datetime

SET @thisMonth = Convert(char(2), DatePart(month, GetDate()))
SET @thisYear = Convert(char(4), DatePart(year, GetDate()))

SET @firstDOM = Convert(datetime, @thisYear + @thisMonth + '01')

SELECT *
FROM myTable
WHERE myDate < @firstDOM
[/CODE]

George
<3Engaged!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-26 : 22:05:02

Good idea but that would require known inputs instead of using the "auto-magic" KHTan showed.

--Jeff Moden

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-29 : 19:41:04

Come again, Jeff?

GetDate() (a known variable) is used to calculate my declarations... Purely for simplification (if that's even a real world!)

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources