May 25, 2010 at 9:25 am
I'm trying to concatenate month and year columns and check the resulting date value to be greater than June 30, 2007. It doesn't work, however, and I'm not sure if this is the correct approach. I've tried several variations but none are working. Any help would be appreciated.
CLACCT - INTEGER
MONTH - INTEGER
YEAR - INTEGER
SELECT TOP 100
CLACCT,
CAST(MONTH AS VARCHAR(2)) + CAST(YEAR AS VARCHAR(4)) AS DATE
FROM cu102 (nolock)
WHERE DATE > 062007
May 25, 2010 at 9:43 am
SELECT TOP 100 CLACCT, DATE
FROM
(
SELECT CLACCT, CAST(MONTH AS VARCHAR(2)) + CAST(YEAR AS VARCHAR(4)) AS DATE
FROM cu102 (nolock)
) D
WHERE DATE > 062007
May 25, 2010 at 10:04 am
_ms65g_ (5/25/2010)
SELECT TOP 100 CLACCT, DATE
FROM
(
SELECT CLACCT, CAST(MONTH AS VARCHAR(2)) + CAST(YEAR AS VARCHAR(4)) AS DATE
FROM cu102 (nolock)
) D
WHERE DATE > 062007
this should be a lesson to never store parts of dates; store the whole date; much easier to work with,a dn you can derive the parts you need any time (month() or year() functions in your case)
the above example's not what you want i think, i'm sure you want to convert to datetime instead; 072006 if treated as a date, is less than 062007, for example, but in the example above it's treated as as text, and would be greater. that's unintended.
it's not a good practice to use nolock. remove that.
--how to build the date:
declare @month int,@year int
SET @month = 3
set @year = 2009
select DATEADD(yy,@year- 1900 ,'19000101 00:00:00.000'),
DATEADD(mm,@month -1,DATEADD(yy,@year- 1900 ,'19000101 00:00:00.000'))
SELECT CLACCT, DATEADD(mm,month -1,DATEADD(yy,year- 1900 ,'19000101 00:00:00.000')) AS DATE
FROM cu102 ) D
WHERE DATE > '20100512 00:00:00.000'
Lowell
May 25, 2010 at 11:20 am
Thanks a lot for the responses!
I hear ya Lowell on breaking up dates and storing separately. Unfortunately this is an old legacy system where the database isn't controlled by the company that owns it (go figure). We have to use NOLOCK since there is so much contention and sensitivity about who is running what (I've fallen and I get up......).
May 25, 2010 at 11:24 am
bigclick (5/25/2010)
oh yeah I've suffered through that same situation before, I feel for you; It might make it easier for you to create views that do some of those calulations to dates for you, then use your views to do the bulk of the work, since it'd have your conversions alreayd in place.
Good luck on your project!
Lowell
May 26, 2010 at 3:26 am
Hi,
another approach would be to convert the year and month values to an int which can be compared and sorted like this:
SELECT d.CLACCT
FROM
(
SELECT CLACCT,YEAR*100 + MONTH AS YEARMONTH as mydate
FROM cu102
) AS d
WHERE d.mydate >= 200707
I often use something like this when working with week numbers.
Steffen.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply