converting a Date - 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)

converting a Date

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-01-19 : 14:01:23

I have a variable @rundate that's in the middle of the month. I would like to convert it into 2 seperate converts, A) Convert to first of the month; B) convert to last day of the month...any help?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-19 : 14:13:47

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0), DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-01-19 : 14:18:11

This is what I have
(select sum(amount)
from billingschedule bs
where convert(varchar(6),bs.billingdate,112) >= convert(varchar(6),@rundate,112) and convert(varchar(6),bs.billingdate,112) <= dateadd(month,1,convert(varchar(6),@rundate,112) - '01'))

I know there is a different way. because this way doesn't work.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-19 : 15:03:23

So put the expressions I gave you into your query

(select sum(amount) 
from billingschedule bs
where bs.billingdate >= DATEADD(mm, DATEDIFF(mm, 0, @rundate), 0) 
and bs.billingdate <= DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, @rundate) + 1, 0))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-19 : 15:20:23

It is usually better and simpler to code date range queries in the form of:

where MyDate >= @StartDate and MyDate < @EndDate

For your query you should do it like this:

where BillingDate >= @CurrentMonthStart and BillingDate < @NextMonthStart

This code does it this way.


select
	sum(amount) 
from
	billingschedule bs
where
	-- Equal to of greater than First day of month at 00:00:00.000
	bs.billingdate >= dateadd(mm,datediff(mm,0,@rundate),0)	and
	-- Less than First day of next month at 00:00:00.000
	bs.billingdate <  dateadd(mm,datediff(mm,0,@rundate)+1,0)

CODO ERGO SUM

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-01-19 : 16:41:15

thanks guys...but I did it a little different, thanks for the Ideas tho.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-19 : 17:01:11

So you asked for help, but figured you know best anyway huh... Want to share that for the benefit of others?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources