Anyone if SQL server has any built-in mechanism for handling Daylight savings?
Mostly for figuring out time passed between two datetime values...
Thanks in advance.Anyone if SQL server has any built-in mechanism for handling Daylight savings?
No, since it's a regional thing anyway
Mostly for figuring out time passed between two datetime values...
Yes...DATEDIFF
What are you trying to do?|||well, like you said I use DATEDIFF to get the amount of time passed between two dates...but (where I live daylight time shifts backwards at 2 (to 1 am) am last sunday of october, and forward at 2 am (to 3 am) on first sunday of april.
basically I want to count time properly...
DATEDIFF(Minute, '2004-04-04 1:30', '2004-04-04 3:30') gives 120 but in reality only 60 minutes passed between the first and second time.|||well, like you said I use DATEDIFF to get the amount of time passed between two dates...but (where I live daylight time shifts backwards at 2 (to 1 am) am last sunday of october, and forward at 2 am (to 3 am) on first sunday of april.
basically I want to count time properly...
DATEDIFF(Minute, '2004-04-04 1:30', '2004-04-04 3:30') gives 120 but in reality only 60 minutes passed between the first and second time.|||You need to build a table that holds (perhaps by region) the dates and times that the switch occurs.
There are some places in the states (by county level even) where the switch does not occur.
Anyway. If the dates EXISTS in the range, then you need to handle it accordingly.
Most likely with a CASE Statement|||If both of the DATETIME values are from the same locale as the server, you could use the GetUTCDate() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_4kkp.asp) to convert them both to UTC, then take the DateDiff() of the UTC DATETIME values. If the DATETIME values are from different locales, let me know how you do it!
-PatP|||It say GetUTCDate() Requires 0 parameters...|||GetUTCDate() works just like GetDate(), but expresses the date/time returned as UTC (also known as Greenwich or Z time) instead of local time. If your app stores server based times in UTC (which is effectively mandated if you have servers in more than one timezone), then life is simple. If you have times stored based on local time, then things get to be really interesting!
Oh yeah, I forgot to mention, there isn't any way to dependably convert a stored local time to Z time, although you can convert Z time to local time if you know which local time.
-PatP
Showing posts with label figuring. Show all posts
Showing posts with label figuring. Show all posts
Sunday, March 25, 2012
Friday, February 24, 2012
Daterange for a daterange
It's late and I'm having a hard time figuring out how the heck to build my where clause.
Here's a sample table with data:
ID int
Value int
StartDate DateTime
EndDate DateTime
1 | 100 | 1/1/2004 | 1/23/2004
2 | 200 | 1/23/2004 | NULL
For all intents and purposes, the second record has a null end date because it's valid until a new value is entered. If I were to update the value again, the 3rd record would look like this.
3 | 300 | 1/24/2004 | NULL
And, since this was updated, I'd go back and update the 2nd record so that I know the End Date (the 3rd record's start date)
2 | 200 | 1/23/2004 | 1/24/2004
Ok, with that said, my application looks at each week in a year, and looks for a valid value for the given date. I need to say "for this week, give me the value." If 2 values fall within the given week, I want to grab the highest (MAX) value.
Any ideas on how I'd structure the SQL statement for this? The where clause is where I'm having funny (hey, that's kinda funny -- where and where).
Anyway, I appreciate any help that you all can give me on this one. It's getting late and my brain is burnt out for the day!Anybody? Sorry to bump this, I just though I may have had a bite or two by now.|||checking your clause...
hope is on the way !|||"for this week, give me the value."
If 2 values fall within the given week,
I want to grab the highest (MAX) value.
what is the value that you're talkign about ?
startdate ?|||ID int
Value int <-- [ this is what I'm getting ]
StartDate DateTime [ used in where clause ]
EndDate DateTime [ used in where clause ]|||and when you say "this week"
what kind of parameter are you sending
date ?
no of week in year ?|||A date range.
i.e. 2/8/2004 - 2/14/2004|||is this what your looking for ?
select max(value)
from range
where '2/8/2004' between startdate and enddate or
'2/14/2004' between startdate and enddate|||Hmm, that may work, BUT, there is still the situation where the most recent value does not have an enddate. I guess I'm going to need to OR that in. Any ideas on that?|||maybe better ?
select *
from range
where '2/8/2004' between startdate and isnull(enddate,'1/1/2999') or
'2/14/2004' between startdate and isnull(enddate,'1/1/2999')|||magical transformation of null to a far-far-far-date|||I think that will work out just fine! :)
Thanks a bunch, I'll work in implementation and check back if I forgot something. You have helped me past my brain fart, thanks!|||on sql i'm good enough to help a bit
Here's a sample table with data:
ID int
Value int
StartDate DateTime
EndDate DateTime
1 | 100 | 1/1/2004 | 1/23/2004
2 | 200 | 1/23/2004 | NULL
For all intents and purposes, the second record has a null end date because it's valid until a new value is entered. If I were to update the value again, the 3rd record would look like this.
3 | 300 | 1/24/2004 | NULL
And, since this was updated, I'd go back and update the 2nd record so that I know the End Date (the 3rd record's start date)
2 | 200 | 1/23/2004 | 1/24/2004
Ok, with that said, my application looks at each week in a year, and looks for a valid value for the given date. I need to say "for this week, give me the value." If 2 values fall within the given week, I want to grab the highest (MAX) value.
Any ideas on how I'd structure the SQL statement for this? The where clause is where I'm having funny (hey, that's kinda funny -- where and where).
Anyway, I appreciate any help that you all can give me on this one. It's getting late and my brain is burnt out for the day!Anybody? Sorry to bump this, I just though I may have had a bite or two by now.|||checking your clause...
hope is on the way !|||"for this week, give me the value."
If 2 values fall within the given week,
I want to grab the highest (MAX) value.
what is the value that you're talkign about ?
startdate ?|||ID int
Value int <-- [ this is what I'm getting ]
StartDate DateTime [ used in where clause ]
EndDate DateTime [ used in where clause ]|||and when you say "this week"
what kind of parameter are you sending
date ?
no of week in year ?|||A date range.
i.e. 2/8/2004 - 2/14/2004|||is this what your looking for ?
select max(value)
from range
where '2/8/2004' between startdate and enddate or
'2/14/2004' between startdate and enddate|||Hmm, that may work, BUT, there is still the situation where the most recent value does not have an enddate. I guess I'm going to need to OR that in. Any ideas on that?|||maybe better ?
select *
from range
where '2/8/2004' between startdate and isnull(enddate,'1/1/2999') or
'2/14/2004' between startdate and isnull(enddate,'1/1/2999')|||magical transformation of null to a far-far-far-date|||I think that will work out just fine! :)
Thanks a bunch, I'll work in implementation and check back if I forgot something. You have helped me past my brain fart, thanks!|||on sql i'm good enough to help a bit
Subscribe to:
Posts (Atom)