Post reply

Date Conversion problem again

  • November 7, 2009 at 1:16 pm

    #156384

    Hey Guys I'm back with another date question. As always the dates I deal with are stored as varchar(8); yyyymmdd.

    Here is the problem. I have to pull all records from the Mstr with a DOB > 71 yrs. Here is what I came up with (but I'm running into conversion problems).

    SELECT PROD, OFFR, Cast(DOB as Bigint) as DOB

    INTO #Tmp

    FROM D_Mstr LEFT JOIN NAM ON Mprod = [NProd]

    WHERE PROD IN ('000100', '000200', '000300')

    AND DateDiff(m, Convert(Varchar(8),DOB ,112), getdate())<=861

    Thanks so much,

    Trudye

  • November 7, 2009 at 2:41 pm

    #1076377

    Hi Trudye

    Hey Guys

    Don't forget the gals ;-). This would exclude some of the greatest people here

    To your problem:

    If your data are already stored as VARCHAR(8) (why?) you don't have to convert them to VARCHAR(8). Use "CONVERT(DATETIME, DOB, 112)"

    Greets

    Flo

  • November 7, 2009 at 7:03 pm

    #1076394

    This will solve the problem of finding everyone 71 years of age or older and still stand a chance of using an index... of course, it's untested because you didn't provide and data IAW the first link in my signature below ;-)...

    SELECT PROD, OFFR, CAST(DOB as Bigint) As DOB

    INTO #Tmp

    FROM dbo.D_Mstr LEFT JOIN dbo.NAM ON Mprod = NProd

    WHERE PROD IN ('000100', '000200', '000300')

    AND DOB <= CONVERT(VARCHAR(8),DATEDIFF(yy,DOB,GETDATE()),112)

    There are several things wrong with all of this and I didn't hazard many guesses... not trying to blast you... I'm trying to educate you a bit...

    One of the things was that you should always use the 2 part naming convention for all SQL objects... I did make that change. The reason to do this is for a bit of extra speed... without using a two pat name, SQL Server looks for a schema name of whoever you're logged in as first. Then, it looks for dbo. If you tell it to look for dbo up front, it saves a little time.

    You don't have any table aliases on any of the columns. That means that the next person who has to troubleshoot your code needs to find all of the column names in the query in both tables. Always use table aliases on all columns.

    As Flo has already stated, storing dates in VARCHAR(8) instead of DATETIME is a form of "Death by SQL". If you don't think so, look at the problems you just had with conversions. Press someone real hard to get that column changed to a DATETIME datatype.

    On top of all of your conversion problems, why on this good green Earth are you formating the DOB column as BIGINT???

    And, last but certainly not least... SQL Server can have column names up to 128 characters... don't abbreviate column names like Product, Offer, Master, etc... I could probably guess what MProd and NProd are, but why should anyone have to? Heh... I don't even abbreviate DOB just in case some foreign nationals who might not know what DOB means have to work on my database or code.

    I won't get into why I think that column and table names shouldn't be in upper case in the code. 😉

    Let me know if the code "fix" I wrote works for you, please.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2009 at 2:12 pm

    #1079270

    Hey Flo my apologies we all know ladies rule ;0)

    Jeff, I appreciate the feedback it will be taken to heart. However I am low lady on the totem pole. I'm a novice and I am working with old sql coders that are allowing me (when I am not supporting produciton) to do a bit of coding on their projects.

    All of the ServerCentral Pros have been asking why my dates and amounts are stored as varchar. I have ask that question of my coworkers but have not received a valid answer. I will continue to see what I can do to change things.

    Thank you all,

    Trudye

  • November 13, 2009 at 4:03 pm

    #1079296

    My guess, old COBOL programmers who haven't changed their ways. 😉

  • November 13, 2009 at 4:19 pm

    #1079301

    trudye10 (11/13/2009)

    Hey Flo my apologies we all know ladies rule ;0)

    Jeff, I appreciate the feedback it will be taken to heart. However I am low lady on the totem pole. I'm a novice and I am working with old sql coders that are allowing me (when I am not supporting produciton) to do a bit of coding on their projects.

    All of the ServerCentral Pros have been asking why my dates and amounts are stored as varchar. I have ask that question of my coworkers but have not received a valid answer. I will continue to see what I can do to change things.

    Thank you all,

    Trudye

    Heh.... I've been there and done that. Sounds like you have a good attitude about it. Hang in there. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply