need to filter for date for anything last month and future.

[sandeep0000]

im using sql sever, would this work if i want to get sts_dt from last month to now?


pgm.sts_dt >=dateadd(month, datediff(month, 0, getdate()) + 1, 0))

 

[AlexCuse]

change your +1 to a -1, and it should work

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

No that would get it from July not May

you need this

dateadd(month, datediff(month, 0, getdate()) - 1, 0)

run this in Query Analyzer

select dateadd(month, datediff(month, 0, getdate()) - 1, 0)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[LNBruno]

Not exactly.

You need to subtract instead of add. And you seem to have one paren more than you need.

< M!ke >
Acupuncture Development: a jab well done.

 

[sandeep0000]

with this filter
pgm1.sts_dt >=dateadd(month, datediff(month, 0, getdate()) - 1, 0)) sts_dt1


im still getting cases that have 2005 values.
seems like all the 2005 data is for month of 11.


2005-11-01 00:00:00.000

 

[SQLDenis]

the pgm1.sts_dt columns is it datetime?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[LNBruno]

That extra paren is still bothering me. Could it be some other part of your query that's pulling them in?

Anyway, this:

select dateadd(month, datediff(month, 0, getdate()) - 1, 0) sts_dt1

returns this:

2007-05-01 00:00:00.000

So my next question would be...data type of pgm1.sts_dt? If datetime, is it localized?

< M!ke >
Acupuncture Development: a jab well done.

 

[sandeep0000]

it is a date time field, what do u mean by localized

 

[sandeep0000]

its a date time field , but all times are 0

 

[gmmastros]

Please run this and post the results here

Select Column_Name, Data_Type
From   Information_Schema.Columns
Where  Table_Name = 'pgm1'
       And Column_Name = 'sts_dt'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sandeep0000]

Select Column_Name, Data_Type
From Information_Schema.Columns
Where Table_Name = 'cis.dbo.cs_pgm '
And Column_Name = 'sts_dt'

i got nothing back

i went to my sql enterprise manager and look at the table
sts_dt

column_name data_type lenght
STS_DT datetime 8

 

[gmmastros]

Can you show us the whole where clause? If I had to guess, you have an OR in there without the appropriate parenthesis.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sandeep0000]

where
cs1.eff_end_dt is null and cindv1.eff_end_dt is null and ipgm1.eff_end_dt is null
and pgm1.eff_end_dt is null and indv1.eff_end_dt is null
and left(nm,2) = 'U5'
and (pgm1.pgm_sts_cd = 'DC'and pgm1.sts_dt >=dateadd(month, datediff(month, 0, getdate()) - 1, 0))) sts_dt1

 

[gmmastros]

OK. more guessing.

I'm guessing this is a derived table (or possibly a subquery). Furthermore, I bet there is something else in the 'bigger picture' that is allowing those records back in.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[onpnt]

I can pretty much guarente that the help you have received thus far given the information (as little as that has been) works

I have created a table from existing data that gathered dates over the last 3 years up to now.

CURDATE name and data type DateTime

[COLOR=blue]Select[/color] 
[CURDATE]
[COLOR=blue]From[/color] 
Tbl
[COLOR=blue]Where[/color] [COLOR=#FF00FF]CURDATE[/color] >= [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]month[/color], [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]month[/color], 0, [COLOR=#FF00FF]getdate[/color]()) - 1, 0)

return is exactly as you asked for

Point of the story, are you going to help us help you yet?

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

One thing I can't guarantee is I can type guarente like that again ;-)

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[sandeep0000]

I Really appricitiate the quick responses and your guys/girls help.

i will try this and see what happens