| Author |
 Topic  |
|
|
vasu4us
Posting Yak Master
102 Posts |
 Posted - 01/18/2008 : 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
India
47971 Posts |
Posted - 01/18/2008 : 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()))) |
 |
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 01/18/2008 : 12:51:40
|
| thank you visakh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/18/2008 : 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" |
|
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 01/22/2008 : 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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/22/2008 : 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" |
|
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 01/23/2008 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/24/2008 : 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" |
|
|
|
yankee_doodle
Starting Member
USA
1 Posts |
Posted - 01/21/2011 : 12:04:17
|
where YOURDATECOLUMN between dateadd(month,-3,getdate())
and getdate() |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 01/21/2011 : 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. |
|
|
|
maughn
Starting Member
Philippines
3 Posts |
Posted - 05/01/2011 : 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. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 05/01/2011 : 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. |
|
|
|
maughn
Starting Member
Philippines
3 Posts |
Posted - 05/01/2011 : 22:26:48
|
yes same date 1 month ago.
thanks nigelrivett |
|
|
|
jblanch62410
Starting Member
3 Posts |
Posted - 08/24/2011 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47971 Posts |
Posted - 08/25/2011 : 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/
|
|
|
|
jblanch62410
Starting Member
3 Posts |
Posted - 08/25/2011 : 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! |
|
|
|
jblanch62410
Starting Member
3 Posts |
Posted - 08/25/2011 : 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. |
|
|
| |
Topic |
|
current date minus 3 months
