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!

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

Recent content by JeffModen

  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...
See more

Part and Inventory Search

Back
Top