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 |
|
grades
Starting Member
4 Posts |
 Posted - 2012-06-09 : 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
52326 Posts |
Posted - 2012-06-09 : 13:46:47
|
| [code]...Where [DateTime] >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,'04:00')AND [DateTime] < = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),'02:00')-1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
grades
Starting Member
4 Posts |
Posted - 2012-06-09 : 17:17:02
|
| Excellent thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-09 : 20:07:38
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
grades
Starting Member
4 Posts |
Posted - 2012-06-09 : 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. |
|
|
|
grades
Starting Member
4 Posts |
Posted - 2012-06-09 : 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
52326 Posts |
Posted - 2012-06-09 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
date / time format with variables