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 |
|
janoloco
Starting Member
2 Posts |
 Posted - 2009-01-11 : 12:55:15
|
Hi guys, i hope you can help me with this....  I have a sales management software for my business (SQL based). The software can export several repots to excel... So i merged a few reports to an Excel linked data sheet to get my daily sales and spends report. I´m trying use the getdate() function to get the registers from currect date(every day). But some times it will return a few registers of the past date too, some times not.Some times i use the functions like this:HAVING ([tblAO_SalesMain].[Date] BETWEEN DATEADD(hh, -12, GETDATE()) AND DATEADD(hh, +10, GETDATE())) AND ([tblAO_SalesMain].[Cancel] =0) ORDER BY [tblAO_SalidaMain].[Key_Suc] ASC, [tblAO_SalesMain].[Key_Store] ASC...but the function misses a few registes of current date, and i have to increase the DATEADD(hh, -12) value to -14 or -16 . so it shows the complete registers of todayBETWEEN DATEADD(dd, -1, GETDATE()) AND DATEADD(dd, +1, GETDATE())) This doesn´t work either.Maybe you know the exact function to show the comple registers of current day only.??Thanks for your help guys. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-11 : 13:11:44
|
| BETWEEN DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-11 : 13:30:17
|
Visakh, that will return next day records dated 00:00:00.000 too.WHERE tblAO_SalesMain.Date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)AND tblAO_SalesMain.Date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)AND tblAO_SalesMain.Cancel = 0
E 12°55'05.63"N 56°04'39.26" |
|
|
|
janoloco
Starting Member
2 Posts |
Posted - 2009-01-11 : 14:26:32
|
| Peso you are the man! Thank you...it worked perfect. i only had to changed WHERE --- To --- HAVING Visakh thanks to you too :) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-11 : 23:55:40
|
Why?Both WHERE and HAVING will produce the same result, but WHERE is more efficient than HAVING, because the HAVING is applied after GROUPing, and WHERE is applied before GROUPing.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
getdate does not work for me