April 21, 2009 at 6:09 am
hi,
I have a table with a column formated in a smalldatetime data type. This table have a lot of records with diferent dates. What i want is to do a select statment where I show only the records with 15 days old, 1 month old and 1 year old.
now i'm using this solution for the year
where table.field1 = 'test' and (Field2 between getdate() -365 and getdate())
and this solution for the month
where table.field1 = 'test' and (Field2 between getdate() -30 and getdate())
table.Field1 = a column with some text in it.
field2 = column formated in a smaldatetime data type.
but, my problem is that the year days isn't allways the same and the mounth days isn't to, and because of that my query isn't very acurat.
can anyone help?
thks in advance
April 21, 2009 at 6:20 am
Look up on BOL for the following datetime functions
DATEADD()
DATEDIFF()
see how you get on...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 21, 2009 at 6:56 am
Christopher Stobbs (4/21/2009)
Look up on BOL for the following datetime functionsDATEADD()
DATEDIFF()
see how you get on...
I have already tryied that, but no luck, can't manage to get it working. can you elaborate some example for my situation?
thks
April 21, 2009 at 7:09 am
Hi there,
The following select statement should give you the dates you are after;
SELECT DATEADD(YYYY, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS [Last Year],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS [This Year],
DATEADD(MM, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS [Last Month],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS [This Month],
DATEADD(DD, -15, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS [Last 15 Days],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS [This Day],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) +1, 0) AS [This Day Inclusive]
I included the last date in case you wanted todays date inclusive.
Hope this helps
Josh
Mao Says RTFM
April 21, 2009 at 7:45 am
ShuaThe2nd (4/21/2009)
Hi there,The following select statement should give you the dates you are after;
SELECT DATEADD(YYYY, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS [Last Year],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS [This Year],
DATEADD(MM, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS [Last Month],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS [This Month],
DATEADD(DD, -15, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS [Last 15 Days],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS [This Day],
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) +1, 0) AS [This Day Inclusive]
I included the last date in case you wanted todays date inclusive.
Hope this helps
Josh
hi...
i don't want to add or subtracts anything from the table, I only want to show the last (15 days, last mounth and last year) data records from the table.
April 21, 2009 at 7:57 am
BK,
You need to use the statements provided as part of your where clause i.e.
SELECT *
FROM table
WHERE table.field1 = 'test'
and (Field2 between DATEADD(YYYY, -1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
and DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
Is this any closer?
Josh
Mao Says RTFM
April 21, 2009 at 7:58 am
When you say last year and last month .
Do you mean the following
today's date = 23/03/2009
Therefore Last year = any day in 2008
Therefore Last month = andy day in the 2 month.
Or do you mean any actual monthly period of 30 or 31 days?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 21, 2009 at 8:07 am
Christopher Stobbs (4/21/2009)
When you say last year and last month .Do you mean the following
today's date = 23/03/2009
Therefore Last year = any day in 2008
Therefore Last month = andy day in the 2 month.
Or do you mean any actual monthly period of 30 or 31 days?
yes...
Last month = any record in the table only from the last month
Last year = any record in the table with the past year.
April 21, 2009 at 8:18 am
this should work according to your criteria:
--Month
WHERE [YourDate] BETWEEN
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
--Year
WHERE [YourDate] BETWEEN
DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0))
Just so you know I done it this way instead of using statements like:
WHERE YEAR([YourDate]) = YEAR(GETDATE()) - 1
The last statement should work for year, but because a function is applied to your column in a where clause it means no indexes can be used... so try and avoid those methods
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 21, 2009 at 8:26 am
Christopher Stobbs (4/21/2009)
this should work according to your criteria:
--Month
WHERE [YourDate] BETWEEN
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
--Year
WHERE [YourDate] BETWEEN
DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0))
Just so you know I done it this way instead of using statements like:
WHERE YEAR([YourDate]) = YEAR(GETDATE()) - 1
The last statement should work for year, but because a function is applied to your column in a where clause it means no indexes can be used... so try and avoid those methods
looks like that works nice. Can you send me the code for the last 15 days?
Can you explain me the -1 in DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy,-1,GETDATE())), 0) and -3 in DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0)) ????
because this Select statment, will run every months, and I don't want to change the SQL code every months...
April 21, 2009 at 8:34 am
ok here is break down of the code:
--Month
WHERE [YourDate] BETWEEN
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
The from part of the date:
Step 1: DATEADD(mm,-1,GETDATE() = get the date from last month.
Step 2: DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())) Find out how months from the Step 1 date to the beginning of time.
Step 3: Add the number of months in Step 2 to the beginning of time, and that will give you the first day of the month for the date in Step 1.
The to part of the date:
Step 1:DATEDIFF(mm,0,GETDATE()) find out how many months from the current date to the beginning of time
Step 2:DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)):Add the number of months in Step 2 to the beginning of time, and that will give you the first day of the month
Step 3: Remove 3ms from the date in step 2 and that will give you the last date of the previous month 3 milliseconds before mid night.
The same principle applies for the Year query, except I'm find how many years have past since the beginning of time etc etc.
Does that help?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 21, 2009 at 8:54 am
Christopher Stobbs (4/21/2009)
ok here is break down of the code:
--Month
WHERE [YourDate] BETWEEN
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
The from part of the date:
Step 1: DATEADD(mm,-1,GETDATE() = get the date from last month.
Step 2: DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())) Find out how months from the Step 1 date to the beginning of time.
Step 3: Add the number of months in Step 2 to the beginning of time, and that will give you the first day of the month for the date in Step 1.
The to part of the date:
Step 1:DATEDIFF(mm,0,GETDATE()) find out how many months from the current date to the beginning of time
Step 2:DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)):Add the number of months in Step 2 to the beginning of time, and that will give you the first day of the month
Step 3: Remove 3ms from the date in step 2 and that will give you the last date of the previous month 3 milliseconds before mid night.
The same principle applies for the Year query, except I'm find how many years have past since the beginning of time etc etc.
Does that help?
Yeap m8, that was a excelent help... So I don't need no change anything in the query for the upcomming months because it will give me allways the last month from the current system date, right?
Can you help me with the Last Week dates? I want only to show only the record with 1 week old.
many thks
April 21, 2009 at 9:07 am
Try give it a go.
So you here is your current date:
15/01/2009
try and right the code.
What you want to try and do is either.
Make you [TableDate] > currentdate - 7 days I'm not sure if you want >= or if you want to go back 6 days that will be defined by your business rules 🙂
Using the same principles I supplied in the other solutions you should find it straight forward to work it out.
show me were you get stuck and I will try and help 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 21, 2009 at 9:09 am
Brain_Killer (4/21/2009)
Christopher Stobbs (4/21/2009)
ok here is break down of the code:
--Month
WHERE [YourDate] BETWEEN
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
The from part of the date:
Step 1: DATEADD(mm,-1,GETDATE() = get the date from last month.
Step 2: DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())) Find out how months from the Step 1 date to the beginning of time.
Step 3: Add the number of months in Step 2 to the beginning of time, and that will give you the first day of the month for the date in Step 1.
The to part of the date:
Step 1:DATEDIFF(mm,0,GETDATE()) find out how many months from the current date to the beginning of time
Step 2:DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)):Add the number of months in Step 2 to the beginning of time, and that will give you the first day of the month
Step 3: Remove 3ms from the date in step 2 and that will give you the last date of the previous month 3 milliseconds before mid night.
The same principle applies for the Year query, except I'm find how many years have past since the beginning of time etc etc.
Does that help?
Yeap m8, that was a excelent help... So I don't need no change anything in the query for the upcomming months because it will give me allways the last month from the current system date, right?
Can you help me with the Last Week dates? I want only to show only the record with 1 week old.
many thks
Not sure if Christopher Stobbs will agree with me, but I'd suggest you take the code and explaination provided and see if you can modify it to meet your requirements. If, after working on it you have problems, come back and post what you have have tried and we'll see what we can do to help. If you figure it out, still come back and post what you did, as it may also benefit others.
April 21, 2009 at 9:12 am
I agree 100% 🙂 I think the tools for your solution have now been provided and working out the last part will provide a good learning platform for you to get a good understanding of what we have shared 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply