September 28, 2010 at 4:02 pm
I am trying to return a MM DD YYYY formatted date as follows:
SELECT CONVERT(DATE,DATEADD(DD,-21,GETDATE()),101)
I just need the date portion of the value returned from DATEADD. The value I get back from this is 2010-09-07.
How can I return just the date portion of this as 09-07-2010 without converting the date to a string?
Thank you for your help!
CSDunn
September 28, 2010 at 4:23 pm
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
I commend you for wanting to stay within the same datatype and not converting to a varchar.
this is one of the coolest things to use once you get your head wrapped around it.
if you select DATEDIFF(dd,0,getdate())(same as select DATEDIFF(dd,'19000101 00:00:00.000',getdate()))
this will return day '40447'(as an integer) , which is the # of days (dd) from the beginning of SQL time 01/01/1900
by adding it to zero(SQL starting date '01/01/1900') with DATEADD(which returns a datetime), you get the first day of the beginning of that day 40447, which is Midnight. Mentally, i sometimes think of this method as "truncating" all the time portion, and then selecting the beginning of the period.
the same concept works when you add weeks, months, years, or quarters...and even hours minutes seconds.....you get the # of [unit you selected] since the beginning of SQL Time, and by adding that #periods to zero(SQL start date), you get the beginning of that period.
run this query so you have everything in front of you:
select
getdate(), --2010-06-16 10:08:47.680
DATEDIFF(dd,0,getdate()), --week '40447' from the beginning of SQL time 01/01/1900
DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) --by adding zero, we return a date that is the first day of the week 5763
here's a collection of "firsts and lasts" i've saved in my snippets:
--find the first business day (Monday) of this month
select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)
--find the last day of the prior month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
--find the third friday of this month:
--14 two weeks plus the M-F offset of 4
select DATEADD(dd,18,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0))
select DATEADD(wk,2,DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)) )
--last business day(Friday) of the prior month...
datename(dw,dateadd(dd,-3,DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0)))
--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--Friday of the Current Week
select dateadd(dd,4,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0))
--First Day of this Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))
--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))
--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
[/quote]
Lowell
September 28, 2010 at 7:53 pm
Check my blog, you'll find some useful date function routines there.
September 29, 2010 at 7:29 am
Thank you for your help!
CSDunn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply