date query - SQL Server Forums

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 query

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 14:47:40

hi,
i try to calculate next calibration date based on the current date(15/03/2010)
see, if iam entering the date today and my frequency period is 5 next date
should be show like "15/08/2010" and i have a variable in sp
nextdate datetime, how i can concocte string to datetime

@monthpart = datepart(mm,@flddoc)
@yearpart = datepart(yy,@flddoc)
set @frequency = (select frequencycode from mst_stdgauagemaster where fldguagekey = @fldguagekey)
set @newmonth = @frequency + @monthpart
if @newmonth > 12
begin
@yearpart = @yearpart +1
end

nextdate = datepart(dd,@flddoc) +"/"+@newmonth+"/"+ @yearpart
this approch is right?

Desikankannan

Ehan
Starting Member

19 Posts

Posted - 2010-03-15 : 14:54:03

is this what you are looking for?

@nextdate = dateadd(mm,@frequency,@flddoc)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-15 : 14:56:07

SELECT DATEADD(MONTH, 5, CURRENT_TIMESTAMP)

-- Removes the Time Portion (if needed)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

-- With period
SELECT DATEADD(MONTH, 5, DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-15 : 14:56:35

select dateadd(month,@frequency,getdate())

should give you the actual date + @frequency (no of months)

Give it a try.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-15 : 14:57:29

The old rocker is to slow again

No, you're never too old to Yak'n'Roll if you're too young to die.

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 15:13:29

if

acutaldate+@frequency is > 12 then year should change 2009 to 2010

quote:
Originally posted by webfred

select dateadd(month,@frequency,getdate())

should give you the actual date + @frequency (no of months)

Give it a try.

No, you're never too old to Yak'n'Roll if you're too young to die.

Desikankannan

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 15:16:32

hi,

my year also should change @mydate +@frequency > 12 then year should change
example
i did calibartion today that is 15/03/2010 and my frequency is 10
then my next calibaration date should be 15/01/2011
hope understand what i need now

quote:
Originally posted by Lamprey

SELECT DATEADD(MONTH, 5, CURRENT_TIMESTAMP)

-- Removes the Time Portion (if needed)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

-- With period
SELECT DATEADD(MONTH, 5, DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0))

Desikankannan

Ehan
Starting Member

19 Posts

Posted - 2010-03-15 : 15:20:49

SELECT DATEADD(mm,10,CAST('03/15/2010' AS DATETIME))

the string above is in mm/dd/yyyy. you may have to CONVERT your dd/mm/yyyy string before you DATEADD

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 15:24:18

hi all
i solve the problem

thanks

quote:
Originally posted by webfred

select dateadd(month,@frequency,getdate())

should give you the actual date + @frequency (no of months)

Give it a try.

No, you're never too old to Yak'n'Roll if you're too young to die.

Desikankannan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-15 : 18:22:35

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources