thread183-1814079
Why do people close a thread like the one being referenced above? The answers work but they're not the only answers and, in this case, probably not the optimal answers.
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is...
Do a search for "t sql like wildcards" (without the quotes) for the documentation and examples of LIKE. It'll be well worth your time.
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By...
Just import them (the character version) into a DATE datatype column and then you can do what you want from there (hint: Using CONVERT... not FORMAT).
And don't use the "underlying numbers" (Date Serial Numbers) from Excel because Excel still thinks that 1900 was a leap year and it is not...
Like I said on the post that Frederico posted, use BCP. Read up on the "CSV" option they came out with in 2017.
For anyone else interested, here's the link I provided to the MS Documentation on the subject. The first article to show up is the documentation on BCP. The second article is one...
In case someone wants to play with some other methods, here's how I created the test table. You'll need to change the table name on my code above if you want to play with it against this data.
--
--===== If it exists, drop the Test Table to make reruns easier in SSMS
DROP TABLE IF EXISTS...
From what I've seen (and I may have missed one somewhere), all of the solutions provided so far have a Non-SARGable equation in the WHERE clause. That will become a whole lot more important when the table gets larger. It seems like they would also skip a month if there were no data for a given...
This looks very much the same but is quite different and quite fast (especially if you have an index on the column). No triangular join here...
SELECT [From],
ISNULL((SELECT TOP 1 o2.[From]-1 AS [To] FROM yourtable o2 WHERE o2.[From] > o1.[From] ORDER BY o2.[From]) ,999999)
FROM...
In SQL Server 2000, that would probably be the way I'd go except that I'd probably use a Temp Table and SELECT/INTO if there were a decent amount of data.
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a...
Nah... forget what I posted... I didn't realize that it makes a performance crushing Triangular Join. Sorry folks.
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
Heh... well?
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
Isn't that what you told it to do?
CONVERT(DATETIME,CONVERT(VARCHAR(2),@I) + '-' + '06-' +
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
If you tweek Boris' code just a little more, you probably don't need the function, either.
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
SELECT ID,Name
FROM gifts
WHERE gifteffdat >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND gifteffdat < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a...
SELECT [From],
(SELECT ISNULL(MIN([From])-1,999999) AS [To] FROM #yourtable o2 WHERE o2.[From] > o1.[From])
FROM yourtable o1
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By...
Heh... seems like it, doesn't it?
Eric, nice job on the proof. It does in fact show that the underlying structure are integers just a MS says. Heh... maybe it'll help me to not think of it that way. ;-)
--Jeff Moden...
markros has it right. You can also cheat...
select * , ICount * 100.0 / FaCount AS Percentage
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
So explain why the float conversion works and the decimal conversion does not.
Nah... never mind.
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
It's also easier for a lot of folks to think of the datetime datatype simply as the number of whole and fractional days since midnight on 1900-01-01 rather than two integers just like it's easier for people to think in Decimal numbers rather than the Binary representation of their nearest FLOAT...
You guys can say and do what you want... the FLOAT conversions work and the DECIMAL conversions work only as well as they would against a FLOAT data type. If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out. Just don't ever tell anyone to do fractional Decimal...
What do you mean that it "performs so badly"? Performance wise?
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.