Another Problem related to dateadd

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)

Another Problem related to dateadd

Author

Topic

sinapra
Starting Member

24 Posts

Posted - 2004-10-19 : 07:13:35

hi guys
Here's my problem, I had this query written in MSAccess. Now after changing a bit, I find this error. My query is :

SELECT DateAdd('d',-6,[Tes].[WeekEndDate]) AS StartDate, Tes.WeekEndDate, TesPeople.WorkerId, TESCorrections.TESCorrections,
TesWorkOrders.WorkOrder, TESCorrections.ProjectNumber, TESCorrections.FinanceProjectSubmitDate
FROM TESCorrections INNER JOIN ((Tes INNER JOIN TesWorkOrders ON Tes.WorkOrders = TesWorkOrders.WorkOrders)
INNER JOIN TesPeople ON Tes.FullName = TesPeople.CostingLogPeople) ON TESCorrections.TES = Tes.CostingLog
WHERE TESCorrections.LaborCorrectionDate Is Not Null AND TESCorrections.ProjectNumber Is Not Null
AND TESCorrections.FinanceProjectSubmitDate Is Not Null
AND TES.PostedDate<=Convert(Datetime, '1899-12-30 00:00:00', 102)
AND TES.PostedDate>Convert(Datetime, '1899-12-30 00:00:00', 102)
AND Tes.Adjusted=1 ORDER BY TesPeople.WorkerId, Tes.WeekEndDate

The error is :
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 specified for dateadd.
/performaindia/audits/finance.asp, line 538

any hints would be a great help.......

Cheers

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-10-19 : 07:19:23

Dont put the d in quotes:
DateAdd(d,-6,[Tes].[WeekEndDate])

Duane.

sinapra
Starting Member

24 Posts

Posted - 2004-10-19 : 07:27:52

Thanks Duane

now i donot see any errors, but not sure if thats the solution since there is no resultset, I will confirm this to my support guys, thanks again.

Duane, I have another similar problem and few of them realy tried to help me in this case earlier but there was no solution,

I am just posting another query and the errors I am getting, any hints in this regards would be of great help too, the query was designed in MSAccess as the earlier one and I am unable to get the right result :

SELECT People.People, People.Name,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/15/2004#,1,0))) AS Created,
Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/15/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))) AS Complete,
People.email FROM (((Register INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status)
LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project) INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register) INNER JOIN People ON StakeHolders.People = People.People
WHERE (((SR_Status.Status)='Active') AND ((StakeHolders.Status)=Yes)) GROUP BY People.People, People.Name,
People.email HAVING
(((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/15/2004#,1,0))))>0)
AND ((Sum(IIf(IsNull([ProjectStatusReport]![Week]),0,IIf([ProjectStatusReport]![Week]=#10/15/2004#,IIf(IsNull([ProjectStatusReport]![Accomplishments])
Or IsNull([ProjectStatusReport]![Plans]),0,1),0))))>0)) ORDER BY People.People

Errors are :

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '!'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'Register'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'SR_Status'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Sum'.

Thanks again

Cheers

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 14:34:30

"IIf" and "!" are Access non-"standards-compliant" extensions, and not supported in SQL Server

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 14:54:42

of course no resultset is returned; look at your criteria:

AND TES.PostedDate<=Convert(Datetime, '1899-12-30 00:00:00', 102)
AND TES.PostedDate>Convert(Datetime, '1899-12-30 00:00:00', 102)

That won't evaluate to "True" very often in this universe ......

EDIT: See below

as for your other Access question, that is a query that references values stored in Access on forms and such; obviously (hopefully) it should be aparent that those value do not exist in SQL Server, only on Access forms in your application, so a quick "translation" is not going to be possible.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 15:19:52

Ah ... the Parallel-Universe version of SQL server. "What would you like me to make the result?"

;-)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 15:29:43

update: I re-read your query and realized that for some reason they use the weird non-standard VBA ! operator to refer to table names and columns; not usually a good idea. I was confused because most often this is used to reference controls on forms or reports within an Access application.

In this particular case, you should be able to replace all occurances of:

[TableName]![FieldName]

with:

[TableName].[FieldName]

which will take care of those. and use this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40845&SearchTerms=access for some handy access-to-sql conversion tips.

finally, remember that Access uses # as a date delimiter, whereas SQL Server has none; surround dates in single quotes '' and/or explicitly convert as necessary to datetype types.

I will probably write up a handy "Access-to-SQL" guide at some point soon in my blogs since this has come up quite a bit lately -- stay tuned.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-19 : 15:31:28

Wonder why it's using that date: isn't it the zero value for the OLE datetime type? Argh! I had a horrible flashback to trying to do date calculations on such values (where time is non-monatonic).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources