DateSerial substitute in SQL Server

[Steven Deetz]

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:

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND

DateSerial(Year(Date()), Month(Date())+1, 0)

When trying this in a View the first error was the Date() function. I replaced the date function with GETDATE(), the Year and Month with DATEPART() to create this function:

BETWEEN DateSerial(DATEPART(yy,GETDATE()),

DATEPART(mm,GETDATE()), 1) AND

DateSerial(DATEPART(yy,GETDATE()),

DATEPART(mm,GETDATE())+1, 0)

But SQL Server then reminded me that DateSerial is not a valid function in SQL Server. Any ideas on creating a way to restrict data in a View to only those transactions occurring in the current month is most appreciated.​

 

[FoFa]

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.

 

[pdx_man]

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

WHERE DATEPART(MONTH,

) = DATEPART(MONTH,GETDATE())
AND DATEPART(YEAR,

) = DATEPART(YEAR,GETDATE())

This would be true in Access, as well. No need to use the DATESERIAL function.​

 

[Steven Deetz]

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

 

[Steven Deetz]

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