getting last 6 months of data only from table with no date column
-
i have a table, and i need to take only the last 6 months data from it. the problem is that the data doesnt have a datetime column, it just has a column stating the year and the month, eg "July, 2006".
how would you go about getting just the last 6 months of data from this table without hard coding dates?
-
Clearly you need to sort out your underlying data, but something like this should do in the meantime...
declare @t table (id int identity(1, 1), v varchar(20))
insert @t
select 'July, 2006'
union all select 'February, 2006'
union all select 'January, 2006'
union all select 'December, 2005'
union all select 'June, 2005'
select * from @t where cast('1 ' + v as datetime) > dateadd(month, -6, getdate())
/*
id v
----------- --------------------
1 July, 2006
2 February, 2006
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Nice.....
--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) -
thats hard coding dates and strings, and thats the last thing i want to do. just to clarify, i inherited this system when i started this job. its been in use for years, so i know that i wont get authorization to change it. i have asked ( as there are more serious issues with the structure of the database than this) .it may be badly done, but its what i have to work with. thanks for the suggestion though
-
Try using a function like:
create function dbo.SillyDate2SQLDate
(
@SillyDate varchar(15)
)
returns datetime
as
begin
declare @RetDate datetime
declare @Months table
(
MonthNbr char(2) collate database_default not null
,SearchMonth varchar(9) collate database_default not null primary key
 
insert into @Months
select '01', 'January' union all
select '02', 'February' union all
select '03', 'March' union all
select '04', 'April' union all
select '05', 'May' union all
select '06', 'June' union all
select '07', 'July' union all
select '08', 'August' union all
select '09', 'September' union all
select '10', 'October' union all
select '11', 'November' union all
select '12', 'December'
select @RetDate = cast(right(@SillyDate,4) + MonthNbr + '01' as datetime)
from @Months
where SearchMonth = left(@SillyDate, len(SearchMonth))
return @RetDate
end
go
select *
from YourTable
where dbo.SillyDate2SQLDate(SillyDate) > dateadd(month, -6, getdate())
To make it more efficient you could try creating a function that returns a table with the appropiate SillyDate strings but somehow I do not think this will be necessary!
-
Is this a joke?
An alternative definition for the 'SillyDate2SQLDate' function is:
create function dbo.SillyDate2SQLDate(@SillyDate varchar(15)) returns datetime
as begin return '1 ' + @SillyDate end
Or you could just do without a function and use what I posted...
select * from YourTable where cast('1 ' + v as datetime) > dateadd(month, -6, getdate())
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Look at the SELECT! That's where the meat of Ryan's example is...
Ryan's post was an example to demonstrate the method for using the "mon yyyy" format as a datetime that you could discriminate against. It's up to you to turn it into a non-hard-coded function or whatever. And, you did ask for precisely 6 months which Ryan's SELECT does nicely... the rest of Ryan's code was just test code to show you that the SELECT works.
So, add a variable that will hold the number of months and, perhaps, the enddate and your done... without hard-coding.
--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) -
Sorry Ryan,
I did not really read the thread and had done something similar with a bitwise date format last week.
Your solution is very elegant.
-
Hey, no worries Ken...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Ken,
Your solution was fine, as well... I was responding to Fintan who apparently didn't read either of your posts very well.
--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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply