GETDATE() throwing exception

Question

I am creating a simple application where i am using MSAccess as database. When i am trying to retrieve the data using below query - i am getting exception undefined function GETDATE()

select *
from tempdata
where dateissue  between DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 0 , 0) 
and  DATEADD(MM,         DATEDIFF(MM, 0, GETDATE()) + 1, - 1 )

can't we use the sql inbuilt methods inside c# code ?? if so how do i solve this problem

i have added one more columnn in the MS Access database and final query is look like SELECT count(Usercategory) as category_count ,Usercategory FROM tempdata where IssueDate between DATEADD(MM, DATEDIFF(MM, 0, DATE()) - 0 , 0) and DATEADD(MM, DATEDIFF(MM, 0, DATE()) + 1, - 1 ) group by category But now i am getting some different error "No value given for one or more required parameters." -- i am not able to figure out any error with this — New Coder, Sep 07 '13 at 01:45
Please mark any answer that solves your original question as solved. Then post a new question that pertains to this new issue. — maccettura, Sep 07 '13 at 01:57

score 8 · Answer 1 · answered Sep 07 '13 at 01:37

8

GETDATE() is not a function inside MSAccess. The equivilant would be:

Now() provides date and time

Date() provides the date

Time() provides the time

answered Sep 07 '13 at 01:37

maccettura

10,514
3
28
35

HansUp · Accepted Answer · 2013-09-07T02:10:32.793

4

Now that you moved past the first problem (there is no GETDATE() function in Access SQL), you have discovered another problem.

The DateAdd Function requires a "String expression that is the interval of time you want to add" as its first argument, Interval. But you're giving it MM instead:

DATEADD(MM, DATEDIFF(MM, 0, DATE()) - 0 , 0)

I don't understand what interval you're trying to add. If you want to add minutes, use ...

DateAdd('n', ...

If you want to add months, use ...

DateAdd('m', ...

If you want to add days, use ...

DateAdd('d', ...

Note DateDiff() also expects an Interval string argument and the allowable values are the same as those for DateAdd().

edited Sep 07 '13 at 02:10

answered Sep 07 '13 at 02:01

HansUp

95,961
11
77
135

Thanks a lot !! Actually i am looking to get the current month data, i.e. data between 1 - 30 of the current month [ here i am not considering time] final query --> SELECT UserCategory FROM User_cat WHERE (IssueDate BETWEEN DATEADD('m', DATEDIFF('m', 0, DATE()) - 0, 0) AND DATEADD('m', DATEDIFF('m', 0, DATE()) + 1, - 1)) GROUP BY UserCategory -- i have data for the current month but still no records are appearing – New Coder Sep 07 '13 at 02:54
2

That's much different than the question you asked here. I suggest you submit that as a new question and include data from `User_cat` showing sample `UserCategory` and `IssueDate` values. Also show the output you want based on those data. That question would interest me, and I would attempt an answer unless someone else gets there first. :-) – HansUp Sep 07 '13 at 03:06
Thanks i have added new question http://stackoverflow.com/questions/18669319/msaccess-query-not-returning-any-result – New Coder Sep 07 '13 at 03:15

score 3 · Answer 3 · answered Sep 07 '13 at 01:37

3

In MS ACCESS equivalent of GETDATE() is DATE()

answered Sep 07 '13 at 01:37

Yuriy Galanter

38,833
15
69
136

i have added one more columnn in the MS Access database and final query is look like SELECT count(Usercategory) as category_count ,Usercategory FROM tempdata where IssueDate between DATEADD(MM, DATEDIFF(MM, 0, DATE()) - 0 , 0) and DATEADD(MM, DATEDIFF(MM, 0, DATE()) + 1, - 1 ) group by category But now i am getting some different error "No value given for one or more required parameters." -- i am not able to figure out any error with this -- can you please advise – New Coder Sep 07 '13 at 01:53

GETDATE() throwing exception

3 Answers3

Linked