Date function

[andyfresh]

Hi

Happy new year to you all,

I cam back after the xmas break to find that one of my date functions isnt working and im unsure why.

This is the code as it is in query Analyzer:

select (convert(char(4),year(getdate())) + '-' + cast(replace(str(Month(DATEADD(DAY,-6,getdate())),2),' ','0') as char(2)) + '-' + cast(replace(str(Day(DATEADD(DAY,-6,getdate())),2),' ','0') as char(2)))

However this returns "2006-12-29" instead of 6 days before the present date.

Can anyone help me with this.

Regards

Andy

 

[jbenson001]

What is it you are trying to do? Are you simply trying to take a date, and minus 6 days?

Jim

 

[SQLDenis]

you forgot to subtract from the year
2 options
select (convert(char(4),year(DATEADD(DAY,-6,getdate())))
+ '-' + cast(replace(str(Month(DATEADD(DAY,-6,getdate())),2),' ','0') as char(2))
+ '-' + cast(replace(str(Day(DATEADD(DAY,-6,getdate())),2),' ','0') as char(2)))

or
select replace(convert(varchar,dateadd(d,-6,getdate()),102),'.','-')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ESquared]

Try

SELECT DateAdd(dd, DateDiff(dd, 6, GetDate()), 0)

 

[andyfresh]

Hi, Thanks for that. It works perfectly.

Andy

 

[ESquared]

Andy, were you responding to me or Denis? I'm not sure if you saw my post.