Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. JeffModen

    thread183-1814079 Why do people

    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...
  2. JeffModen

    How to display part number based on search string by chemical id ?

    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...
  3. JeffModen

    GOING FROM DD/MM/YYYY TO MM/DD/YYYY

    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...
  4. JeffModen

    How to export 20 milion rows to only one file ?

    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...
  5. JeffModen

    Go back a Year using year and Month fields

    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...
  6. JeffModen

    Go back a Year using year and Month fields

    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...
  7. JeffModen

    Need help with loop, variable and insert

    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...
  8. JeffModen

    Need help with loop, variable and insert

    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...
  9. JeffModen

    Need help with loop, variable and insert

    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
  10. JeffModen

    RE: Interesting Case Statement

    Heh... well? --Jeff Moden ------------------------------------------------------------------------------- "RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
  11. JeffModen

    need to modify this SQL

    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
  12. JeffModen

    simple function help

    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
  13. JeffModen

    Current Month vs hard coding the month in SQL

    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...
  14. JeffModen

    Need help with loop, variable and insert

    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...
  15. JeffModen

    Getdate Function

    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...
  16. JeffModen

    Percentage Calculation

    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
  17. JeffModen

    Getdate Function

    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
  18. JeffModen

    Getdate Function

    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...
  19. JeffModen

    Getdate Function

    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...
  20. JeffModen

    last delimter in string

    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

Part and Inventory Search

Back
Top