Days Difference Between 2 Dates
-
Actually, the date rounds up from noon, onwards, not just after noon.
-
L' Eomot Inversé (10/2/2012)
...Yes, I change the avatar every few months; and switch languages (and messages) in the signature message quite often too. Avoids being too much the same all the time.
From Google translate:
When ruigeas you country gheallaidh, if you were not him t'aire, coinnichidh Sasunnach riut,
These plìon him, day dh'innseadh you gun God gave, father, brother, just two anns domain.
Either your Irish is horrible or Google translate needs some work. :hehe:
-
Dave62 (10/2/2012)
L' Eomot Inversé (10/2/2012)
...Yes, I change the avatar every few months; and switch languages (and messages) in the signature message quite often too. Avoids being too much the same all the time.
From Google translate:
When ruigeas you country gheallaidh, if you were not him t'aire, coinnichidh Sasunnach riut,
These plìon him, day dh'innseadh you gun God gave, father, brother, just two anns domain.
Either your Irish is horrible or Google translate needs some work. :hehe:
Or it isn't Irish ?
Anyway, what's wrong with the proposed translation, it's more coherent than many of the emails I receive.
-
From Google translate:
When ruigeas you country gheallaidh, if you were not him t'aire, coinnichidh Sasunnach riut,
These plìon him, day dh'innseadh you gun God gave, father, brother, just two anns domain.
Either your Irish is horrible or Google translate needs some work. :hehe:[/quote]
Two things there: 1) It isn't Irish (neither gaeilge nor gaoluinn), it's gàidhlig (scottish gaelic - which Google Translate currently doesn't support at all, although plenty of other google stuff supports the language) - so the Google translate language recognition is broken. 2) Google translate needs not just sopme but a hell of a lot of work on Irish (and on all the other languages I've ever tried it on); it really is awful.
One observation is that it appears to make no attempt to parse things sensibly: so if you ask it to translate, for example, "John pants when he over excercises; to pant is painful" you get "Pantalones de John cuando más ejercicios. Para pantalón es doloroso" - and the words Pantalones, más, ejercicios, and pantalón not only have (completely) the wrong meanings, they are also the wrong parts of speech! What what "Para" is doing in there is anyone's guess - are they perhaps trying to produce an infinitive of intent out of "trouser"? A second observation is that when translating from something other than English to something else other than English it does two translations - one from the first language to English, then another from English to the second language (clearly visible because you can see the two sets of errors compounded together) - this may not be true for all pairs of languages, it's just true for all I've tried.
The sentence is a quotation from a rather famous poem ("Cruaidh?")by a rather famous poet (Ruaridh MacThomais, aka Dereck or Derick Thomson), and means "When you reach the promised land, if you don't look out, a smirking Englishman will meet you to tell you that God, his uncle, has given him the rights to the land". Incidentally there was one typo in it ("bi" was written "bhi") which I've now corrected; and my capitalisation is different from Ruaridh's. You can find the whole poem pplus a translation at oi]http://www.scottishpoetrylibrary.org.uk/poetry/poems/cruaidh[/i]
Tom
-
Thanks for the question Dwain.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Good question Dwain... thanks for submitting it. (I wonder how many that got this correct ran the code first...)
One minor nit-picking comment. You initialize the dates like this:
DECLARE @Date1 DATETIME = '2012-08-28 11:53:00', @Date2 DATETIME = '2012-08-29 13:25:00'
I assume that this date format is used so that it can be loaded in any language? Well, the proper ISO date format for these dates would be to have the time separated from the date with a "T", like so:
DECLARE @Date1 DATETIME = '2012-08-28T11:53:00', @Date2 DATETIME = '2012-08-29T13:25:00'
<shameless plug> Learn about this and more at our new book. </shameless plug>
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
archie flockhart (10/2/2012)
"Interesting" behaviour:DECLARE @date datetime
SET @date='2012-10-01 13:21:00'
SELECT CAST(@date AS int)
result: 41182
SELECT CAST(CAST(@date AS float) AS int)
result: 41181
This occurs because casting DATETIME to INT rounds times after 12:00:00 UP to the next day, while casting FLOAT to INT truncates the decimal portion. Therefore, the cast of DATETIME to FLOAT in your example yields 41181.55625; CASTing that back to INT yields 41181.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Jason Wolfkill
-
wolfkillj (10/2/2012)
archie flockhart (10/2/2012)
"Interesting" behaviour:DECLARE @date datetime
SET @date='2012-10-01 13:21:00'
SELECT CAST(@date AS int)
result: 41182
SELECT CAST(CAST(@date AS float) AS int)
result: 41181
This occurs because casting DATETIME to INT rounds times after 12:00:00 UP to the next day, while casting FLOAT to INT truncates the decimal portion. Therefore, the cast of DATETIME to FLOAT in your example yields 41181.55625; CASTing that back to INT yields 41181.
Yes, that's the interesting inconsistency I intended to highlight. Some CAST combinations will round the data, others will truncate it.
It works strangely the other way round too:
declare @f float
set @f=40000.6
select cast(@f as int) -- 40000
select cast(cast(@f as datetime) as int) -- 40001
Not that you'd actually choose to convert FLOAT to the nearest rounded INT by going via an intermediate DATETIME, but it's an interesting quirk of the definitions, dont you think ?
But try it with other types, and it behaves differently:
declare @m money
set @m=40000.6
select cast(@m as int) -- 40001
select cast(cast(@m as datetime) as int) --40001
-
Nice question Dwain. Datetime questions are always helpful as it seems that so many systems treat dates differently.
-
L' Eomot Inversé (10/2/2012)
Two things there: 1) It isn't Irish (neither gaeilge nor gaoluinn), it's gàidhlig (scottish gaelic - which Google Translate currently doesn't support at all, although plenty of other google stuff supports the language) - so the Google translate language recognition is broken. 2) Google translate needs not just sopme but a hell of a lot of work on Irish (and on all the other languages I've ever tried it on); it really is awful.
One observation is that it appears to make no attempt to parse things sensibly: so if you ask it to translate, for example, "John pants when he over excercises; to pant is painful" you get "Pantalones de John cuando más ejercicios. Para pantalón es doloroso" - and the words Pantalones, más, ejercicios, and pantalón not only have (completely) the wrong meanings, they are also the wrong parts of speech! What what "Para" is doing in there is anyone's guess - are they perhaps trying to produce an infinitive of intent out of "trouser"? A second observation is that when translating from something other than English to something else other than English it does two translations - one from the first language to English, then another from English to the second language (clearly visible because you can see the two sets of errors compounded together) - this may not be true for all pairs of languages, it's just true for all I've tried.
The sentence is a quotation from a rather famous poem ("Cruaidh?")by a rather famous poet (Ruaridh MacThomais, aka Dereck or Derick Thomson), and means "When you reach the promised land, if you don't look out, a smirking Englishman will meet you to tell you that God, his uncle, has given him the rights to the land". Incidentally there was one typo in it ("bi" was written "bhi") which I've now corrected; and my capitalisation is different from Ruaridh's. You can find the whole poem pplus a translation at oi]http://www.scottishpoetrylibrary.org.uk/poetry/poems/cruaidh[/i]
I thought the question was interesting, until I read Tom's detour into spoken languages. What I love about the languages of man, as opposed to the languages of machines, is their color, exressivity, flexibility, and nuance. Just the kinds of things that aren't practical for a computer language. Makes me want to write novels - until I realize that it's a lot easier to earn a living writing in the langauges of machines than in the languages of man.
While I've never seen computer code that made my heart sing, I have seen code that made me cry! :hehe:
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
WayneS (10/2/2012)
Good question Dwain... thanks for submitting it. (I wonder how many that got this correct ran the code first...)One minor nit-picking comment. You initialize the dates like this:
DECLARE @Date1 DATETIME = '2012-08-28 11:53:00', @Date2 DATETIME = '2012-08-29 13:25:00'
I assume that this date format is used so that it can be loaded in any language? Well, the proper ISO date format for these dates would be to have the time separated from the date with a "T", like so:
DECLARE @Date1 DATETIME = '2012-08-28T11:53:00', @Date2 DATETIME = '2012-08-29T13:25:00'
<shameless plug> Learn about this and more at our new book. </shameless plug>
Everyone's a critic! :w00t:
Actually, I know about the ISO format but think it is less readable so I rarely use it.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
PhilPacha (10/2/2012)
Actually, the date rounds up from noon, onwards, not just after noon.Thanks Phil. An important clarification.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Interesting question Dwain..Kept me thinking for a while 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks for the question Dwain!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Lokesh Vij (10/2/2012)
Interesting question Dwain..Kept me thinking for a while 🙂+1
nice question
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply