I don’t know why I’m having trouble with this, but I am.
I have a table with a year/month stored as a char(6) in the following format: 200406 would be June 2004.
Here’s my challenge: I need to display rows where the above mentioned column is older than x months old.
In my latest incarnation I tried breaking the date apart into year and month, then getting the day from getdate() and concatinating it all together, and comparing it to dateadd(m,-3,getdate())
I continually get a conversion error.
I will post a version of my SQL if asked, but I don’t want to taint anyones advice.
Why convert to a date at all? You have a char field that will sort in date order based on string comparisons. Given values like 200311, 200312, 200401, 200402, etc. you can construct a criterion date x-months old using date functions. Say you want everything older than three months ago, so you compare to 200405 and then use a SQL query with a clause like
WHERE myDate <= '200405'
This will work just using a string comparison as long as you’ve zero-padded your months.
This doesn’t work because SQL Server doesn’t know that ‘200406’ is June 2004. If you want to use dateadd(), you have to use a date format that SQL Server recognizes. SQL Server only recognizes date formats with a month, day and year. You can’t have a date with just month and year.
There are several workarounds:
(1) Take micco’s suggestion.
(2) A better solution would be to use two int columns - one for month and the other for year.
(3) An even better (although more involved solution) is to use a datetime column. Write a trigger that will convert the input value (‘200406’) to 06-01-02004 (or whatever other arbitrary day of the month you choose) 00:00:00 whenever an insert or update is done. Then use the query you wrote above.
If you need any help with this, feel free to email me (I’ve been doing SQL Server development for the last five years).
I may take you up on that, Zev.
Changing the db isn’t an option. I need to run a select query on the column as it is and be able to arbitrarily filter out older or newer dates.
I never tried to write a query against the column as it stands now, as I know it wasn’t an acceptable date format.
What I did was write a query that converted the column to mm/dd/yyyy format using datepart(dd,getdate()) or day(getdate()), and then filling in mm and yyyy with the info from the column. But then I received errors when I tried to compare that date to getdate().
When I get back to work tomorrow, (EST - Atlanta) I may drop you an email, or take micco’s suggestion which seems quick and easy.
Dates are always fun and tricky. Here’s some pseudo-code that should work.
Input PAST_MONTHS
If PAST_MONTHS == 0
Get ALL year_month records
Else
CY = get_current_year();
CM = get_current_month();
CT = (CY * 12) + CM; /* CT = total months in current year_month */
CT = CT - PAST_MONTHS; /* Calculate your year_month search argument */
CY = CT/12;
CM = remainder(CT/12);
search_arg = ascii(CY) + ascii(CM); /* Format the search argument as ascii YYYYMM */
Get ALL year_month records where year_month < search_arg;
select monthYearColumn
from enrightsTable
where monthYearColumn > cast(year(dateadd(m,@monthCount,getdate()))as varchar(4)) + right(‘0’ + cast(month(dateadd(m, @monthCount,getdate()))as varchar(2)),2)
order by monthYearColumn
I think everyone is out thinking themselves here. SQL Server (or Sybase, or whatever) is smart enough to convert a YYYYMMDD char value into a date. So just give them one to use.
Try this:
select * from myTable where datepart(mm, convert(datetime, '200406' + '01' , 112)) <= datepart(mm, dateadd(mm, -3, getdate()))
Just make sure that “200406” part is your column name. Should work just fine
I just realized that my reply was a little too oversimplified since it wholly neglects coparing years. Its simple enough to account for however, and can be done like so:
select * from myTable
where myWierdDateColumn <= left(convert(char(8), dateadd(mm, -3, getdate()), 112), 6)
