SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Two SQL updates into One?

    Can I make this query into a single one? Maybe like a If Else statement? In a Stored Procedure I could do it, but i'm not sure how with 'Selects and updates'...?

    Code:
    Update WhseOpenOrders 
    Set LateIndicator = 'L'
    FROM WhseOpenOrders 
    WHERE PromiseDate < CONVERT( varchar(20), getdate(), 101) 
    
    Update WhseOpenOrders 
    Set LateIndicator = '*'
    FROM WhseOpenOrders 
    WHERE PromiseDate BETWEEN CONVERT( varchar(20), getdate(), 101) AND CONVERT( varchar(20), getdate() + 14, 101)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you don't need to convert a datetime column to a string in order to compare it to another datetime column

    assuming, of course, that your PromiseDate is actually a datetime column and not a string itself
    Code:
    update WhseOpenOrders 
       set LateIndicator 
         = case when PromiseDate < getdate()
                then 'L'
                else '*' end
      from WhseOpenOrders 
     where PromiseDate < dateadd(d,14,getdate())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Owen Sound
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using SQL sever Enterprise, and it always converts my datetime columns to a string. I change it when I use the Query for final production.

    I was thinking of using a case statement, and that looks like you beat me too it. Just a few changes, and she works like a charm. Thanks again, this site rocks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sql server always converts datetime strings to integers

    internally, datetime values are always stored as a pair of integers

    when you display a datetime value, sql server does not show you its internal integer values, instead, it converts the datetime value to a string

    i know it's confusing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •