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? :eek:

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= yyeartry this as a template:


declare @.str char(06)
select @.str = '060228' -- Feb 28 2006
select convert(datetime,@.str)|||How about

where convert(varchar(10), dateadd (dd, -1, getdate()), 101) = loc_86

Depending on your delimiter, of course.|||How closely do you want to compare them? Getdate() returns results in milliseconds. Are you just trying to match on the day?|||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?|||Open BOL. Search the index for CONVERT. Read the topic "CAST and CONVERT". All the formats are given there.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:|||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:select *
from YourTable
where datediff(day, getdate(), convert(datetime, left(DateString,2) + '-' + substring(DateString, 3, 2) + '-' + right(DateString,2), 10)) = 0|||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.|||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.|||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.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:SELECT Replace(Convert(VARCHAR(10), GetDate(), 1), '/', '')-PatP|||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: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|||Thanks so much! The following script worked:

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