DateSerial substitute in SQL Server (1 Viewer)

I have been searching through the forum for a way to create a View that only lists transactions that have occurred in the current month. I was able to create an Access Query that used DateSerial to create the following Criteria under the TransactionDate field:

I am not sure I understand, since GetDate() returns the current date. I don't see where you are minipulating a columns date. Typically you would do something like this.
AND MyDate Between Date1 and Date2
NOW you can create a date such as
MyDate between cast(month(getdate()) as varchar2) + '/01/' + cast(year(GetDate()) as varchar(4)) AND cast(month(adddate(m,1,getdate())) as varchar2) + '/01/' + cast(year(adddate(,m,1,GetDate())) as varchar(4))
Which if I did it right would appear (give month = 3)
MyDate between '03/01/2006' AND '04/01/2006' and the time factor would be 00:00:00 for both. So the 04/01/2006 would ONLY be factor is you are not tracking time stamps. If not then subtract a day from the result of add one month (to give last day of prior month) and move forward.
You can also use CONVERT but it is basically the same.

Hmmm ... seems like you are doing an awful lot to get the transactions that occurred in the current month. How about:

WHERE DATEPART(MONTH,

Fofa,

Thanks for the function idea. I got it partially working with this:

BETWEEN CAST(MONTH(GETDATE()) AS varchar(2)) + '/01/' + CAST(YEAR(GETDATE()) AS varchar(4)) AND CAST(MONTH(GETDATE()) + 1 AS varchar(2)) + '/01/' + CAST(YEAR(GETDATE()) AS varchar(4))

This returns a beginning of the month date for the current month and for the next month. Try as I might I cannot get the AND portion of the function statement to equal the end of the month. I can create a field using

DATEADD(m, 1, GETDATE()) - DATEPART(dd, GETDATE())

that will create a field posting a date listing the end of the month. When I substitute this expression for the '/01/' in the expression at the top I get an error message saying "Syntax error converting datetime from string". I have tried your earlier suggestion of changing the next month's first day by subtracting 1 but I continually get an error message saying that the function cannot be converted to a valid date/time or a message saying that there is a function argument count error. I have triple checked the syntax and it appears to be good.

BETWEEN CAST(MONTH(GETDATE()) AS varchar(2)) + '/01/' + CAST(YEAR(GETDATE()) AS varchar(4)) AND CAST(CAST(MONTH(GETDATE()) + 1 AS varchar(2)) + '/01/' + CAST(YEAR(GETDATE()) AS varchar(4))-1)

Any ideas on how to subtract a day from the beginning of the month or create an end of month date is most appreciated!

If I understand Access' DateSerial function correctly, DateSerial can perform the above calculations for you. You only need to enter the current date. When you enter a Zero in the day place of DateSerial(Year, Month, Day) the function returns the last day of the month.

Steve

pdx_man,

Thanks for the tip! It works perfectly. I went into the SQL language and built the following WHERE clause:

WHERE (DATEPART(Month, dbo.tbl_TransactionsMain.TransactionDate) = DATEPART(MONTH, GETDATE())) AND (dbo.tbl_TransactionDetail.DepositAmount = 0) AND (dbo.tbl_TransactionDetail.BilledAmount <> 0) AND (DATEPART(Year, dbo.tbl_TransactionsMain.TransactionDate) = DATEPART(Year, GETDATE()))

This shows only bills for the current month and gives me another way of thinking about dates and date ranges

Thanks again!

Steve