Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2005
    Location
    North Carolina
    Posts
    11

    Question Unanswered: Comapring date fields

    I am trying to compare two date fields; one is a string and one is a getdate() field. I am trying to get them into the same format so I can compare them. What am I doing wrong???

    select convert(integer, substring(loc_86, 1, 2)) as tmonth,
    convert(integer, substring(loc_86, 3, 2)) as tday,
    convert(integer, '20'+right(loc_86, 2)) as tyear,
    datepart(month, (dateadd(day, -1, (getdate())))) as ymonth,
    datepart(day, (dateadd(day, -1, (getdate())))) as yesterday,
    datepart(year, (dateadd(day, -1, (getdate())))) as yyear
    from ub_chg_tbl join ubmast_tbl
    on (ubmast_tbl.patient_nbr = ub_chg_tbl.patient_nbr)
    where tmonth = ymonth and tday = yesterday and ty= yyear
    Amber Hardee, DBA

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    try this as a template:

    Code:
     
    declare @str char(06)
    select @str = '060228' --  Feb 28 2006
    select convert(datetime,@str)

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How about
    Code:
    where convert(varchar(10), dateadd (dd, -1, getdate()), 101) = loc_86
    Depending on your delimiter, of course.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How closely do you want to compare them? Getdate() returns results in milliseconds. Are you just trying to match on the day?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2005
    Location
    North Carolina
    Posts
    11
    I'm only trying to match the day.

    When I use this script:

    Convert(VarChar(12),GetDate(),112) as '112'

    I get a date in a format of YYMMDD.

    I need the date in the format of MMDDYY. How can I do this?
    Amber Hardee, DBA

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Open BOL. Search the index for CONVERT. Read the topic "CAST and CONVERT". All the formats are given there.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Apr 2005
    Location
    North Carolina
    Posts
    11
    There is not a format listed for MMDDYY. Is this really not possible?
    Amber Hardee, DBA

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should NOT be storing date values as strings. Possibly the most common noob DBA mistake of all time. I strongly urge you to change your datetype to datetime.

    That said, this should work for you:
    Code:
    select	*
    from	YourTable
    where	datediff(day, getdate(), convert(datetime, left(DateString,2) + '-' + substring(DateString, 3, 2) + '-' + right(DateString,2), 10)) = 0
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2005
    Location
    North Carolina
    Posts
    11
    I wish we could change it. We are in healthcare and this database is federally regulated, so we are not allowed to change the format of the fields.
    Amber Hardee, DBA

  10. #10
    Join Date
    Apr 2005
    Location
    North Carolina
    Posts
    11
    Now I am receiving this error:

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    Amber Hardee, DBA

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because you have an invalid date in your table, because you are not using datetime datatypes.

    Format your datestring as 'YYYY-MM-DD' and run it through the ISDATE() function to find the bad records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mrshardee2
    There is not a format listed for MMDDYY. Is this really not possible?
    Code:
    SELECT Replace(Convert(VARCHAR(10), GetDate(), 1), '/', '')
    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mrshardee2
    Now I am receiving this error:

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    As Sallah once told Indy: Bad Dates.

    The following shows how to weed them out relatively painlessly:
    Code:
    CREATE TABLE #patp_date_demo (patp_date CHAR(6))
    
    INSERT INTO #patp_date_demo (patp_date)
       SELECT '122505' UNION
       SELECT '022900' UNION
       SELECT '022901' UNION
       SELECT '063104' UNION
       SELECT '131211'
    
    SELECT patp_date
       FROM #patp_date_demo
       WHERE 0 = IsDate(Stuff(Stuff(patp_date, 5, 0, '-'), 3, 0, '-'))
    
    DROP TABLE #ptp_date_demo
    -PatP

  14. #14
    Join Date
    Apr 2005
    Location
    North Carolina
    Posts
    11

    Talking

    Thanks so much! The following script worked:

    REPLACE(CONVERT(varchar(10), DATEADD(day, - 1, GETDATE()), 1), '/', '') AS Yesterday
    Amber Hardee, DBA

Posting Permissions

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