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 |
|
figmo
Starting Member
18 Posts |
 Posted - 2012-07-08 : 23:43:59
|
| I am using an orders table that has a DateTime column that represents the UTC DateTime an order was created. I've run into a problem that seems like there should be a simple solution for - but haven't been able to find it.Given this simple query:SELECT * FROM ORDERS WHERE OrderDate >= @StartDate AND OrderDate <= @EndDateWorks as expected - EXCEPT for orders placed later in the day on the EndDate. Because the UTC DateTime stored is tomorrow (from my timezone)Certainly there has to be some way to tell SQL Server that we want all orders placed on or before today - IN MY TIME ZONE?This is part of a shopping cart software so I had no hand in the design. I could add columns - I am the administrator - but want to avoid this to make upgrades easier. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-09 : 00:29:39
|
| [code]SELECT * FROM ORDERS WHERE OrderDate >= DATEADD(ss,-1 * DATEDIFF(ss,GETDATE(),GETUTCDATE()),@StartDate) AND OrderDate <= DATEADD(ss,-1 * DATEDIFF(ss,GETDATE(),GETUTCDATE()),@EndDate)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
figmo
Starting Member
18 Posts |
Posted - 2012-07-09 : 11:27:41
|
| Thank you visakh. Your query did not work as posted - but you gave me the tools I needed. I was able to figure it out from there. For others who may come across this thread: I had to remove the '-1 *' from his query. Like this:SELECT * FROM ORDERS WHERE OrderDate >= DATEADD(ss,DATEDIFF(ss,GETDATE(),GETUTCDATE()),@StartDate) AND OrderDate <= DATEADD(ss,DATEDIFF(ss,GETDATE(),GETUTCDATE()),@EndDate)Because we want to add the offset to the search date - not subtract it. The DATEDIFF as you have it will return an inverted UTC offset (postive number in the west, negative number in the east) which means we must ADD it to the start/end date - not subtract it.Anyway - query works fine now. Thanks for your help! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-09 : 11:33:37
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
UTC DateTime in query