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.
Author |
Topic |
junior6202
Starting Member
45 Posts |
Posted - 2014-08-21 : 13:51:39
|
So I am recreating some tables in Sql Server that originate in Access. I am having a bit of a problem converting the Dateadd Access Syntax to TSQL. Here is the query in Access and what i have so far in TSQL. I tried several Datediff functions but nothing seems to work. Thanks in advance for your help.ACCESS:SELECT Sales_ItemMonthly.ITEMNMBR, Sum(nz([QtySold],0)) AS SumOfQtySold, Sum(nz([Value],0)) AS SumOfValueFROM Sales_ItemMonthlyWHERE (((Sales_ItemMonthly.Month) Between DateAdd("yyyy",-1,MthFirstday(Date())) And (mthfirstday(Date())-1)))GROUP BY Sales_ItemMonthly.ITEMNMBR;TSQL: SELECT ITEMNMBR, ISNULL(SUM(QtySold), 0) AS SumOfQtySold, ISNULL(SUM(Value), 0) AS SumOfValue, MonthFROM cgi_master.dbo.Sales_ItemMonthlyGROUP BY ITEMNMBR, Month |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-21 : 14:16:43
|
between DATEADD(year, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) and DATEADD(day, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
junior6202
Starting Member
45 Posts |
Posted - 2014-08-21 : 14:31:18
|
Thank you very much Tara. It worked perfectly. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-21 : 16:20:37
|
Slightly shorterWHERE Sales_ItemMonthly.Month >= DATEADD(MONTH, DATEDIFF(MONTH, '19010101', GETDATE()), '19000101') AND Sales_ItemMonthly.Month < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101') Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|