# T-SQL with a date. **URL:** \ **Category:** Factual Questions\ **Created:** [September 22, 2004, 9:15pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557 "2004-09-22T21:15:29Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [September 22, 2004, 9:15pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/1 "2004-09-22T21:15:29Z")
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. E3 ---
**Author:** ![FatBaldGuy](https://avatars.discourse-cdn.com/v4/letter/f/ecd19e/32.png) [@FatBaldGuy](https://boards.straightdope.com/u/FatBaldGuy)\ **Post date:** [September 22, 2004, 9:34pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/2 "2004-09-22T21:34:05Z")
Sorry, I read the thread title and thought “What kind of a geek goes on a date and talks about T-SQL?” :smack: ---
**Author:** ![micco](https://avatars.discourse-cdn.com/v4/letter/m/5f8ce5/32.png) [@micco](https://boards.straightdope.com/u/micco)\ **Post date:** [September 22, 2004, 9:34pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/3 "2004-09-22T21:34:55Z")
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 ```auto WHERE myDate <= '200405' ``` This will work just using a string comparison as long as you’ve zero-padded your months. ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [September 22, 2004, 10:12pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/4 "2004-09-22T22:12:03Z")
> [@Enright3](#): > > 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. > > E3 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). Zev Steinhardt ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [September 23, 2004, 12:05am UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/5 "2004-09-23T00:05:12Z")
> [@zev\_steinhardt](#): > > 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). > > Zev Steinhardt 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. Thanks everybody! Keep em coming. E3 ---
**Author:** ![ccwaterback](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@ccwaterback](https://boards.straightdope.com/u/ccwaterback)\ **Post date:** [September 23, 2004, 4:43am UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/6 "2004-09-23T04:43:32Z")
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; ``` Endif ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [September 23, 2004, 4:56am UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/7 "2004-09-23T04:56:39Z")
Did you try doing it backwards? Change today’s date into a # akin to your myDate column and then compare numbers. DateYear = DatePart(“yyyy”, Date()) DateMonth = DatePart(“m”, Date()) If Len(DateMonth) \< 2 Then DateMonth = ‘0’ & DateMonth NewDateNumber = DateYear & DateMonth If NewDateNumber \<\> “” Then NewDateNumber = CInt(NewDateNumber) (so newdatenumber = 200409 for today) Then do some math comparison between that new # and your column… If MyDate \>= (NewDateNumber - 3) Then …whatever. A year and 3 months ago would be NewDateNumber - 103. ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [September 23, 2004, 4:58am UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/8 "2004-09-23T04:58:58Z")
BTW my code is more VBScript than tsql, so convert as you will. My bad, I don’t do much T-SQL 🙂 ---
**Author:** ![Enright3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/enright3/32/2897_2.png) [@Enright3](https://boards.straightdope.com/u/Enright3)\ **Post date:** [September 23, 2004, 5:32pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/9 "2004-09-23T17:32:50Z")
> [@ZipperJJ](#): > > Did you try doing it backwards? Change today’s date into a # akin to your myDate column and then compare numbers. > > DateYear = DatePart(“yyyy”, Date()) > DateMonth = DatePart(“m”, Date()) > If Len(DateMonth) \< 2 Then DateMonth = ‘0’ & DateMonth > NewDateNumber = DateYear & DateMonth > If NewDateNumber \<\> “” Then NewDateNumber = CInt(NewDateNumber) > (so newdatenumber = 200409 for today) > > Then do some math comparison between that new # and your column… > > If MyDate \>= (NewDateNumber - 3) Then …whatever. A year and 3 months ago would be NewDateNumber - 103. That’s pretty much what I did. declare @monthCount int set @monthCount = -3 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 or something like that. ---
**Author:** ![Omniscient](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/omniscient/32/3290_2.png) [@Omniscient](https://boards.straightdope.com/u/Omniscient)\ **Post date:** [September 23, 2004, 9:25pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/10 "2004-09-23T21:25:50Z")
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: ```auto 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 ---
**Author:** ![Omniscient](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/omniscient/32/3290_2.png) [@Omniscient](https://boards.straightdope.com/u/Omniscient)\ **Post date:** [September 23, 2004, 9:44pm UTC](https://boards.straightdope.com/t/t-sql-with-a-date/265557/11 "2004-09-23T21:44:43Z")
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: ```auto select * from myTable where myWierdDateColumn <= left(convert(char(8), dateadd(mm, -3, getdate()), 112), 6) ```