Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 current date minus 3 months

Author  Topic 

vasu4us
Posting Yak Master

102 Posts

Posted - 2008-01-18 : 11:06:12
I have a requirment where i need to check 2 columns yyyy and mm which contain year and month.
this condetion is for a where clause to get the data between current date goint back to 3 months.I came up with this but its problem when year changes

WHERE YYYY + MM BETWEEN CAST(CONVERT(VARCHAR(4),GETDATE(),112)+ right('00' + convert(varchar,(month(getdate())-3)),2) AS INT) AND
CAST(CONVERT(VARCHAR(6),getdate(),112) AS INT)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 11:15:42
where (yyyy <=YEAR(GETDATE()) AND YEAR >YEAR(DATEADD(mm,-3,GETDATE())))
AND (mm<=MONTH(GETDATE()) AND mm>MONTH(DATEADD(mm,-3,GETDATE())))
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 2008-01-18 : 12:51:40
thank you visakh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 16:28:01
Hmmm... What about 2007-02 and 3 months back? Or 2007-11 and 3 months back?

WHERE 12 * yyyy + mm >= 12 * YEAR(GETDATE()) + MONTH(GETDATE())



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 2008-01-22 : 14:25:06
now i am running into a new issue with the where condetion. when ther is a changein year the first condetion ie yyyy(year)gives 2007 and 2008 and the second condetion mm(month)is giving 1 and 10. which means in 2007 1st to 10th month and 2008 1st 10th month.
My requirment is to get data for running three months ie oct this date till today. any suggestions
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 18:49:12
Are you for real?
I posted a solution for you 4 days ago.

WHERE 12 * yyyy + mm >= 12 * YEAR(GETDATE()) + MONTH(GETDATE())



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 2008-01-23 : 12:42:02
Gr8 Peso I could never guess something like that, how could you think so differently.
i was all th etime stuck with

where int(ltrim( rtrim("SQLUSER"."BTT120".YYYY)) CONCAT ltrim( rtrim("SQLUSER"."BTT120".MM))) between
int(ltrim( rtrim(char(YEAR(CURRENT DATE)))) concat right(ltrim(rtrim('00' concat char(month(CURRENT DATE)))),2)) and
int(ltrim( rtrim(char(YEAR(CURRENT DATE- 3 months)))) concat right(ltrim(rtrim('00' concat char(month(CURRENT DATE- 3 months)))),2))

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-24 : 02:47:56
I have learned from MVJ.
Dates are nothing more than a sequence number from a certain fixed point in history.
In SQL Server it just happens to be 19000101 by default.
You can temporarily change this date to any toher date to fit your purpose.

In my example I changed the "fixed point" to January 1, 0000.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

yankee_doodle
Starting Member

1 Post

Posted - 2011-01-21 : 12:04:17

where YOURDATECOLUMN between dateadd(month,-3,getdate())
and getdate()
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-21 : 12:18:05
quote:
Originally posted by yankee_doodle


where YOURDATECOLUMN between dateadd(month,-3,getdate())
and getdate()

Might want to read the original post again.
Go to Top of Page

maughn
Starting Member

3 Posts

Posted - 2011-05-01 : 21:12:06
can you help me the same problem. i have a given date column and i will subtract exactly 1 month from the given month.

thanks.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-01 : 21:53:48
You mean same date 1 month ago?
dateadd(mm,-1,dte)
start of previous month?
dateadd(mm,datediff(mm,0,dte)-1,0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

maughn
Starting Member

3 Posts

Posted - 2011-05-01 : 22:26:48
yes same date 1 month ago.

thanks nigelrivett
Go to Top of Page

jblanch62410
Starting Member

3 Posts

Posted - 2011-08-24 : 14:52:37
Same issue but diffrent ranges I need to know how to get the query to do a current and 12 months past time fram so ex.201108 through 201008. Then a second query will do a 3 month comparison as in ex.201107,201108 and 201008 hopefully in that order. I could do both in access using datediff but it does not seem to work in sql05. I would like to move the access querys to sql for more efficiency and less id 10t errors.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 02:04:11
it should be below i think

where datefield >= dateadd(yy,datediff(yy,0,getdate())-1,0)
and datefield <dateadd(mm,datediff(mm,0,getdate())+1,0)
)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jblanch62410
Starting Member

3 Posts

Posted - 2011-08-25 : 07:45:30
I have tried what you posted above. It gives me a datetime conversion error. The creator of the database that I am grabbing data from created the table with the year and date fields seperated as varchar(255). I am a newbie to SQL but have done access for years. and I am able to do it access but am looking to simplify that app and push most of the work to the server.
I used this to accomlish it after joining the date fields with a "/" seperator.And telling it I only want 1-13
DateDiff("m",Now(),[qrydatejoin]![datejoin])+13.

I am not sure if that is the issue and I should just rebuild the tables. If thats the case I would have to look into how to have the table self populate with the month/year and in another column how many working days there are in a month. or I am in for a lot of typing. But I might be missing the point and i can evaluate a varchar. Thanks for all of your time!
Go to Top of Page

jblanch62410
Starting Member

3 Posts

Posted - 2011-08-25 : 09:17:27
Thank you all for the time in trying to help with this issue. I have resolved by taking my own advise and manually adding that actual date field. then using the datediff function as described above. It wokes great. Again I do appreciate your time.
Go to Top of Page
   

- Advertisement -