dateadd? - 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

General SQL Server Forums

New to SQL Server Programming

dateadd?

Author

Topic

jl
Starting Member

6 Posts

Posted - 2008-06-27 : 07:29:21

hi @all...

hope you can help me...i've got a column (dayid) with dates between 2007-07-01 and 2008-06-30.

my task is to take all dates between 2007-07-01 and 2007-12-31 and add a year to them so that i finally get a column from 2008-01-01 to 2008-12-31.

i've already tried this:
--update <table>
--set dayid = select datepart(yyyy,dayid)+1
--where dayid between '20070701' and '20071231'

but he doesn't like it ;-)

thnxx in advance for your suggestions....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 07:31:53

Are you using SQL 2005?

jl
Starting Member

6 Posts

Posted - 2008-06-27 : 07:44:33

sorry! yep... 2005

edit:
next try didn't work again:

--update <table>
-- set dayid = (select dateadd(yyyy,1, dayid) from <table>
-- where dayid between '20070701' and '20071231')
-- where dayid between '20070701' and '20071231'

"eeergh"

:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 08:01:42

You can use this code for dates upto approx 6 years difference:-

declare @startdate datetime,@enddate datetime

select @startdate='2007-07-01',@enddate='2008-06-30'


SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values 
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<=@EndDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 08:08:43

CTE method:-

declare @startdate datetime,@enddate datetime

select @startdate='2007-07-01',@enddate='2008-06-30'

;
WIth Date_CTE (Date) AS
(SELECT @StartDate
UNION ALL
SELECT DATEADD(dd,1,Date) FROM Date_CTE  WHERE Date <@EndDate 
)

select * FROM Date_CTE 
OPTION (MAXRECURSION 500);

jl
Starting Member

6 Posts

Posted - 2008-06-27 : 08:19:26

thank you first...

but i receive an error message.
will try to translate this into english:

"Meldung 242, Ebene 16, Status 3, Zeile 15
Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs."

this means:
"during the convertion of a char datatype into datetime, the datetime-value is out of the valid range."

this error already appears, when i reduce the command to the two first lines:
declare @startdate datetime, @enddate datetime
select @startdate='2007-07-01', @enddate='2008-06-30'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 08:27:05

quote:
Originally posted by jl

thank you first...

but i receive an error message.
will try to translate this into english:

"Meldung 242, Ebene 16, Status 3, Zeile 15
Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs."

this means:
"during the convertion of a char datatype into datetime, the datetime-value is out of the valid range."

this error already appears, when i reduce the command to the two first lines:
declare @startdate datetime, @enddate datetime
select @startdate='2007-07-01', @enddate='2008-06-30'

thats because date format of server is different use this before the query:-

SET DATEFORMAT ymd

jl
Starting Member

6 Posts

Posted - 2008-06-27 : 08:39:04

thanks for your help...!!!!!!
i think i must have a look back into this on monday.

i'll study your suggestions and post a comment when i get further....

thnxx & have a nice weekend!!! ;)

jl
Starting Member

6 Posts

Posted - 2008-07-01 : 05:40:27

hey.... the solution was pretty simple:

quote:
UPDATE <table>
SET dayid = dateadd(yyyy,1, dayid)
WHERE dayid LIKE '%2007%'

thank you anyway for your support!!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 05:59:29

quote:
Originally posted by jl

hey.... the solution was pretty simple:

quote:
UPDATE <table>
SET dayid = dateadd(yyyy,1, dayid)
WHERE dayid LIKE '%2007%'

thank you anyway for your support!!!!!!!!

sorry i couldnt read your mind from your first post. see what you've asked for

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-01 : 16:05:32

quote:
Originally posted by jl

hey.... the solution was pretty simple:

quote:
UPDATE <table>
SET dayid = dateadd(yyyy,1, dayid)
WHERE dayitd LIKE '%2007%'

thank you anyway for your support!!!!!!!!

If dayid is of DATETIME datatype, better use

UPDATE <table>
SET dayid = dateadd(yyyy,1, dayid)
WHERE dayid >='20070101' and dayid<'20080101'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources