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)
 date calculation

Author  Topic 

neil_akoga
Yak Posting Veteran

56 Posts
Posted - 2008-10-14 : 09:34:01
ok, something that's bugging me and i could do in vb but i've always been told to get my data sorted before i get it into my code. it's probably simple but it's wracking my brain

i wanna select * from the table below (tblJobs)
jobID/jobDescription/approvalDate(dateTime)
where the approvalDate is not more than 28 days since the current date today

any help appreciated :)

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2008-10-14 : 09:39:32
select * from tbljobs where approvaldate < getdate()+27
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-10-14 : 09:42:01
where approvalDate<dateadd(day,datediff(day,0,Getdate()),28)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-10-14 : 09:48:58
quote:
Originally posted by karthik_padbanaban

select * from tbljobs where approvaldate < getdate()+27

That wont be accurate
See my first reply

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

neil_akoga
Yak Posting Veteran

56 Posts
Posted - 2008-10-14 : 10:14:33
ok i tried both, neither seemed to do anything, this is how my select looks

SELECT jobID, location, package, profile, apply, submissionDate, approvalDate, approved, submittedBy, position, SubmittedName, email
FROM avwJobs
WHERE (approvalDate IS NOT NULL) AND (approvalDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 28))
Go to Top of Page

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2008-10-14 : 10:24:16
quote:
Originally posted by neil_akoga

ok i tried both, neither seemed to do anything, this is how my select looks

SELECT jobID, location, package, profile, apply, submissionDate, approvalDate, approved, submittedBy, position, SubmittedName, email
FROM avwJobs
WHERE (approvalDate IS NOT NULL) AND (approvalDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 28))


It's working fine what's the error message you got
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2008-10-14 : 10:24:56
Are you looking for approval dates 28 days after today or 28 days before today?


CODO ERGO SUM
Go to Top of Page

neil_akoga
Yak Posting Veteran

56 Posts
Posted - 2008-10-14 : 10:50:37
sorry, i think my explanations been a bit crap. people submit jobs, they get approved (approvalDate is created as date now) then they are shown on a web page for 28 days from approval date, after 28 days i need them to disappear from the front end but stay in the database

i didn't get an error, my datetime is in the following format
13/10/2008 17:56:14 day/month/year/time
Go to Top of Page

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2008-10-14 : 11:05:57
quote:
Originally posted by neil_akoga

sorry, i think my explanations been a bit crap. people submit jobs, they get approved (approvalDate is created as date now) then they are shown on a web page for 28 days from approval date, after 28 days i need them to disappear from the front end but stay in the database

i didn't get an error, my datetime is in the following format
13/10/2008 17:56:14 day/month/year/time


the format you specified is in dd/mm/yyyy. but datetime accepts mm/dd/yyyy format. check with isdate('13/10/2008 17:56:14 ') it returns 0 for your date.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-14 : 11:07:03
quote:
Originally posted by neil_akoga

sorry, i think my explanations been a bit crap. people submit jobs, they get approved (approvalDate is created as date now) then they are shown on a web page for 28 days from approval date, after 28 days i need them to disappear from the front end but stay in the database

i didn't get an error, my datetime is in the following format
13/10/2008 17:56:14 day/month/year/time

then just retrieve only data from your table
WHERE DATEDIFF(dd,approvalDate,GETDATE())<=28
Go to Top of Page

neil_akoga
Yak Posting Veteran

56 Posts
Posted - 2008-10-14 : 11:09:18
quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by neil_akoga

sorry, i think my explanations been a bit crap. people submit jobs, they get approved (approvalDate is created as date now) then they are shown on a web page for 28 days from approval date, after 28 days i need them to disappear from the front end but stay in the database

i didn't get an error, my datetime is in the following format
13/10/2008 17:56:14 day/month/year/time


the format you specified is in dd/mm/yyyy. but datetime accepts mm/dd/yyyy format. check with isdate('13/10/2008 17:56:14 ') it returns 0 for your date.


as i understand it the sql server i am using is setup for european d/m/y format
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-14 : 11:14:38
[code]SELECT jobID, location, package, profile, apply, submissionDate, approvalDate, approved, submittedBy, position, SubmittedName, email
FROM avwJobs
WHERE (approvalDate > DATEADD(day, DATEDIFF(day, 0, GETDATE())-28,0))[/code]
Go to Top of Page

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2008-10-14 : 11:17:17
quote:
Originally posted by neil_akoga

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by neil_akoga

sorry, i think my explanations been a bit crap. people submit jobs, they get approved (approvalDate is created as date now) then they are shown on a web page for 28 days from approval date, after 28 days i need them to disappear from the front end but stay in the database

i didn't get an error, my datetime is in the following format
13/10/2008 17:56:14 day/month/year/time


the format you specified is in dd/mm/yyyy. but datetime accepts mm/dd/yyyy format. check with isdate('13/10/2008 17:56:14 ') it returns 0 for your date.


as i understand it the sql server i am using is setup for european d/m/y format


check your getdate() what format is it in?
Go to Top of Page

neil_akoga
Yak Posting Veteran

56 Posts
Posted - 2008-10-14 : 11:30:31
karthik_padbanaban - i'm not really sure what you want me to say. my datetime format is dd/mm/yyyy

thanks visakh, that works!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-14 : 12:49:04
quote:
Originally posted by neil_akoga

karthik_padbanaban - i'm not really sure what you want me to say. my datetime format is dd/mm/yyyy

thanks visakh, that works!

welcome

i think he was trying to find out your regional settings

a date format of dd/mm/yyyy suggests that your reginal settings is English UK
Go to Top of Page

neil_akoga
Yak Posting Veteran

56 Posts
Posted - 2009-01-27 : 06:11:37
ok, this worked up until the end of the year. now it's 2009, it's stopped working. anyone got any ideas?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-27 : 09:17:21
do you have any records in your table for last 28 days? then only below will work

SELECT jobID, location, package, profile, apply, submissionDate, approvalDate, approved, submittedBy, position, SubmittedName, email
FROM avwJobs
WHERE (approvalDate > DATEADD(day, DATEDIFF(day, 0, GETDATE())-28,0))
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site