July 17, 2012 at 7:30 am
Adding a set of functions with 'UTC' appended would help.
GetDate() would continue to return the local time according to the server.
GetDateUtc() would return UTC time according to the server.
I happen to work for an organization that is within one time zone and always will be (hint: government) therefore I can safely ignore UTC for most things.
However, I would still love to have the GetDateUtc() function, because for some inserts (for example a threat delivered through a web form) it would be better to record the UTC time. (Helps with the police investigation 😎 )
July 17, 2012 at 7:34 am
Chris.C-977504 (7/17/2012)
GetDateUtc() would return UTC time according to the server.
The naming's not exactly what you're asking for, but the function exists:
SELECT GetUTCDate()
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
July 17, 2012 at 7:35 am
I do NOT like the idea of moving from numbers to the use of text within the SQL Server functions.
It would cause both a lot of extra processing and added complexity -- would 'EDC' be resolved as UTC-5 or UTC+10, or worse maybe base it on a database setting which makes it ever harder to maintain/debug).
SELECT CONVERT( datetime, @dt, 'dd.mm.yy') must determine what you meant by 'dd.mm.yy' but with the numeric, it knows without a doubt because it is defined in the spec that you have to look at (probably regularly).
The extra processor cycles to interpret the text is reason enough not to do it (remember this CONVERT statement might be used on a large number of records within a single query... on multiple fields in that query).
I would like one function that will strip off a time component and another that will strip of the date. (or ignore if that's in 2012, I don't have it in house yet so I haven't checked)
July 17, 2012 at 7:38 am
Tao Klerks (7/17/2012)
Chris.C-977504 (7/17/2012)
GetDateUtc() would return UTC time according to the server.The naming's not exactly what you're asking for, but the function exists:
SELECT GetUTCDate()
FANTASTIC! Thanks for letting me know
I don't care that the name didn't match what I picked, I'm just happy it is there.
July 17, 2012 at 7:39 am
Steve Jones (7/17/2012)
CONVERT still has crazy numeric codes for conversion. Wouldn’t it make more sense to do this:
DECLARE @dt datetime
SELECT @dt = GETDATE()
SELECT CONVERT( datetime, @dt, 'dd.mm.yy')
Hey Steve. Yes, yes it would make things a ton easier to do this type of thing. And in fact it has been added to SQL Server 2012 via the new FORMAT function:
SELECT FORMAT(GETDATE(), 'D') AS [DateTimeWithStandardFormat],
FORMAT(GETDATE(), 'D', 'he') AS [DateTimeWithStandardFormatAndLocale-Hebrew],
FORMAT(GETDATE(), 'dd.mm.yy') AS [DateTimeWithCustomFormat],
FORMAT(GETDATE(), 'dd.mm.yy', 'he') AS [DateTimeWithCustomFormatAndLocale-Hebrew],
FORMAT(CONVERT(DATETIME, '2012-07-10 12:34:56.789'), 'D', 'he') AS [ConvertedStringWithStandardFormatAndLocale-Hebrew]
And for people still on SQL Server 2005 or 2008/2008R2, you can do the exact same thing via the free SQLCLR library SQL# (SQLsharp) found at http://www.SQLsharp.com/.
SELECT SQL#.Date_Format(GETDATE(), 'D') AS [DateTimeWithStandardFormat],
SQL#.Date_Format(GETDATE(), 'D', 'he') AS [DateTimeWithStandardFormatAndLocale-Hebrew],
SQL#.Date_Format(GETDATE(), 'dd.mm.yy') AS [DateTimeWithCustomFormat],
SQL#.Date_Format(GETDATE(), 'dd.mm.yy', 'he') AS [DateTimeWithCustomFormatAndLocale-Hebrew],
SQL#.Date_Format(CONVERT(DATETIME, '2012-07-10 12:34:56.789'), 'D', 'he') AS [ConvertedStringWithStandardFormatAndLocale-Hebrew]
Here is a list of supported "Standard" DateTime formats:
http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.80).aspx
Here is a list of supported "Custom" DateTime format string:
http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.80).aspx
Nakul Vachhrajani (7/17/2012)
Personally, I believe that formatting and local adjustments should be performed by the application/UI layer and not the database. Dates in the database should be stored as UTC and fetched as UTC. If the application wants it in the Eastern time or in the Western time, the application should convert the UTC accordingly.
Hello Nakul. For the most part I agree. BUT, as with most things, we need to keep in mind the ever-present mantra: "It Depends". Sometimes we do have output requirements at the database layer and hence these abilities should be available. When they should be used is a matter of education, experience, and best practices.
Also, sometimes applications will store dates twice: once as UTC and once in a local timezone as defined when a user creates their account. This is done so that the local time values can be indexed as converting those values on the fly can be very expensive, not to mention the requirement for keeping track of when Daylight Savings Time starts and ends in various regions over the years since that changes over time.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 17, 2012 at 7:40 am
Personally, I believe that formatting and local adjustments should be performed by the application/UI layer and not the database
Um ... that's all well and good, provided the App/UI is aware of the provenance of the time information - what was in the "mind" of the source at the time it wrote it.
My headache had its roots in a data source that could have been configured to use UTC but didn't - although only in one "real" time zone (London, England), all datetimes were post-DST adjustment, and individual Logs/rows had multiple datetime columns supposedly representing time of origin and time of transport to Staging.
Besides, as a rule, data calculations should be kept close to (ie in) the back-end right? How you gonna get an interval (eg DATEDIFF()) when the begin and end points are (sometimes!) different animals? How you gonna know when they are different when the source (back-end) can't or won't tell you?
Ahem ... the red mist nearly got me there .... my apologies.
I think we have fully exposed the reason why this area is poorly supported - its difficult!
I still maintain that to deal properly with datetimes, there should be a datetime object with properties to expose time zone and DST settings, and methods to present different datetime values for different zone/DST property settings.
Let's encapsulate, people!
Just my $0.02 worth ....
Russ
July 17, 2012 at 7:49 am
Alex Fekken (7/17/2012)
You might want to consider that there are 5 different definitions of 'CST' (http://www.timeanddate.com/library/abbreviations/timezones/). But even if we did have codes that are well known and unique, I think that what you want might not be that straightforward.In theory I suppose SQL Server could know exactly about the past definitions (or at least the relative time differences) of all timezones (including e.g. whether or not here was daylight savings for your flavour of 'CST' in, say, 1861).
But what about events in the future? Do you want the outcome of your query to change potentially with an update to the timezone definitions? Daylight savings is only one reason why this might (or is likely to?) happen.
Or would you be happy if this was only reliable for past and/or nearby dates?
Exactly right.
Has everyone already forgot the massive trouble that occurred a few years ago when the US changed DST? For those who were off planet at the time, it wasn't as simple as updating your Windows XP OS. You also had to update Microsoft Office. If someone had Lotus Notes (no idea why anyone uses that anymore) it needed a separate update. Don't even mention all the Java updates that were required because Sun and Microsoft hate each other! Then add in the fact that none of the companies could get it right, so after updating Java you had to do so again, then again. Each update included code that broke applications that relied on older versions of the "write once use anywhere" alleged capabilities of Java, because those updates were coded differently.
Seriously, why would we want to add more complexity to something? Leave this at the OS level.
Now as to other posts already about how hard dates are to work with, that I agree with. Offering SQL functions that work with dates but that rely on the OS to provide things would be great.
Dave
July 17, 2012 at 10:07 am
djackson 22568 (7/17/2012)
. . . Leave this at the OS level. . . . Offering SQL functions that work with dates but that rely on the OS to provide things would be great.
Absolutely. Azure SQL or SQL Database (or whatever is that version of SQLS2012 called today) machines have two clones, and they are in at least two different geolocations. It means that the system has to run on UTC and as paul.knibbs observed, you better convert it to the user's time at the front end.
July 17, 2012 at 10:33 am
I have had to deal with time zones in my line of work for the past 10 years. I've learned way more than I care to or think should be necessary. It sounds like most of us agree on the topic at hand.
While I know it would never happen, I've had a very radical idea that I'd like to throw out there. What if instead of complaining about and discussing ways to work around the problem, we just address the problem directly.
Completely get rid of time zones and Daylight Saving Time across the entire world!!!!
So no matter where you are in the world, right now it is 16:21, period.
Time zones and the use of Daylight Saving Time is all political. Nothing more. So my idea would only be possible if all the governments in all the world could actually agree on something. Which obviously would never happen.
I live in Arizona, one of only a couple places in the United States that does not obey Daylight Saving Time. And let me tell you, it is VERY nice! You guys don't know what you're missing.
When George W. Bush signed the Energy Policy Act of 2005, they claimed it would save energy. How? You still have the exact same amount of daylight in the day. The only difference is you "sliding" the time around. The reason this "saves" energy is because people stay up until midnight and have their lights on for four hours. Then they don't get out of bed until 9 AM and have already wasted 3 hours of daylight.
So again, instead of addressing the real problem, we work around it. Why not just tell the lazy people to go to bed earlier and get up earlier!
Well, I'll stop ranting now. I told you it was pretty radical.
I also have a lot of code for dealing with time zones if anybody is interested, but none of it is in SQL, sorry.
July 17, 2012 at 10:51 am
I'm all for saving DateTime data in UTC, even more so as I have read through here.
I primarily work web sites, and the one thing that bugs me is when I goto other sites and they are formatting basically on the sites local time.
Generally, the local information is available to the sites by accessing javascript, so the app should be able to figure out what the correct time zone is and adjust accordingly.
While I do see the validity in creating the functions for the DB to use, time zones and offsets and their related locales are subject to change; a perfect instance would be the State of Michigan, which has 75000 people an hour behind the rest of the state
July 17, 2012 at 10:56 am
kevin77 (7/17/2012)
Completely get rid of time zones and Daylight Saving Time across the entire world!!!!So no matter where you are in the world, right now it is 16:21, period.
I think you're maybe only partly being facetious. Getting rid of timezones would be impossible but getting rid of DST would not be. Most people don't realize how insane it really is. And I noticed no one in the thread touched on this little gem:
The state of Arizona in the US does not observe DST. I'm not really sure why but I have a feeling it's somehow related to their hatred of the federal government and their non-observance of Dr. Martin Luther King Day as well.
The Navajo Nation reservation, which falls inside Arizona, Utah and New Mexico observes DST as referenced here: http://en.wikipedia.org/wiki/Navajo_Nation#Daylight_Saving_Time
Now, the Hopi Reservation, which is actually completely surrounded by the Navajo Nation (and only within the borders of Arizona) follows Arizona's lead.
I can only imagine that geographical locations near the International Date Line have similar quirks.
No pun intended, but isn't it about time we ditch DST altogether?
July 17, 2012 at 11:12 am
If I remember correctly there is a part of Indiana that also does not recognize DST. So, depending on Daylight Savings Time or Standard Time, they are eith in the Eastern or Central time zone.
July 17, 2012 at 11:24 am
I think you're maybe only partly being facetious. Getting rid of timezones would be impossible
No, it wouldn't. Time zones weren't even introduced in the United States until the mid 1800's due to the railroads. However, they still "adjusted" time such that when the sun was highest in the sky, it meant it was noon. With my idea, noon could exist across the entire world at the same instant. It just means that some people will be in bed sleeping because now, where they are in the world, noon would now effectively mean "midnight" to them.
It would be a huge culture shock. But it is very possible.
Just think of all the coding and processor power that would be saved if we got rid of time zones and Daylight Saving Time. Talk about saving power and energy!
July 17, 2012 at 11:24 am
paul.knibbs (7/17/2012)
In two minds about this myself. Yes, it would certainly be handy to have these conversion functions in SQL server, but on the other hand, the old mantra of "do the formatting in the front-end application, not the database" surely applies?
"It depends". If you have to talk via a linked server to an Oracle-based third-party interface requiring you to use dd-mon-yy, then there's no "front-end application" - it has to be done in the DB.
I keep a copy of the date CONVERT BOL page on my office wall - too many dumb codes and formats to clutter my grey matter with unnecessarily!
Please, MS, yes we have the new format T-SQL command in SQL2012, but an overloaded date-format-not-number would be so nice. Agree with Steve Jones. How hard can it be, MS?
July 17, 2012 at 11:25 am
Lynn, that's not the case anymore but when it was it was also quite odd. Indiana had it's own timezone for a while. Parts of the state were Central and parts were "Eastern (Indiana)". This was different than Eastern by I think 1/2 an hour. Essentially in between Eastern and Central. When the Energy Act of 2005 was passed to move the start and end of DST in the US, the state figured it would be too expensive to maintain the extra timezone and dropped it. Most of Indiana is Eastern (even as far west as Indianapolis, the capital). A small part of western Indiana is Central.
Viewing 15 posts - 16 through 30 (of 110 total)
You must be logged in to reply to this topic. Login to reply