| Author |
 Topic  |
|
|
grades
Starting Member
4 Posts |
 Posted - 06/09/2012 : 13:40:12
|
Hi,
I am linking an Access database to some SQL views i have put together. What i need to do is import certain info for a 1 month range into Access. Our business day ends around 2am and begins at 4am, so I need to make something like this:
Where (DateTime BETWEEN DATEADD(day, - DAY(GETDATE()) + 1, DATEADD(month, - 1, GETDATE())),04:00:00.0) AND DATEADD(day, - DAY(GETDATE()), GETDATE()), 2:00:00.0)
If it's not clear what i am going for, the datetime (which is a field in my SQL table) should be between the first day of last month at 4am, and the last day of last month at 2am.
I know the syntax is all wrong, but hopefully someone can point me in the right direction?
Thanks,
grades |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/09/2012 : 13:46:47
|
...
Where [DateTime] >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,'04:00')
AND [DateTime] < = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),'02:00')-1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
grades
Starting Member
4 Posts |
Posted - 06/09/2012 : 17:17:02
|
| Excellent thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/09/2012 : 20:07:38
|
welcome 
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
grades
Starting Member
4 Posts |
Posted - 06/09/2012 : 20:08:40
|
Sorry, but I'm not sure this is 100%. And i should have mentioned this, but the 2am on the last day actually has to be the morning of the first day of the next month (this month). I haven't confirmed this, but I believe it's not picking up the last day.
when i take off the -1 at the end i think it's closer.. I apologize for not being able to understand how you've used these three functions in this way together. |
Edited by - grades on 06/09/2012 20:09:54 |
|
|
|
grades
Starting Member
4 Posts |
Posted - 06/09/2012 : 20:17:35
|
Ah nevermind, yeah i had to remove the -1 and then add the months together.
Thanks again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/09/2012 : 21:03:52
|
quote:
Originally posted by grades
Sorry, but I'm not sure this is 100%. And i should have mentioned this, but the 2am on the last day actually has to be the morning of the first day of the next month (this month). I haven't confirmed this, but I believe it's not picking up the last day.
when i take off the -1 at the end i think it's closer.. I apologize for not being able to understand how you've used these three functions in this way together.
that was not clear from initial explanation. in that case just remove -1 and you're good to go with it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
date / time format with variables
