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.
| Author |
Topic |
|
ScottBot
Starting Member
16 Posts |
 Posted - 2011-03-03 : 09:40:56
|
| Hi,I was wondering if anyone knows a way to trim GetDate() value to show only the day of the month so for instance rather than having 31/03/2011I get 31.I would like to base a calculation on the days in a month but am having troubles. I can get it to display the last day of a month and convert it from a datetime value to varchar to use it in a calculation but I just cant get the days section only.Thanks in advance |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-03 : 09:46:49
|
[code]dateadd(day, datediff(day, 0, getdate()), 0)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-03-03 : 09:48:07
|
| From SQL Server 2008 Books Online:Functions That Get Date and Time Parts:http://msdn.microsoft.com/en-us/library/ms186724(v=SQL.100).aspx#GetDateandTimePartsCODO ERGO SUM |
|
|
|
ScottBot
Starting Member
16 Posts |
Posted - 2011-03-03 : 10:05:23
|
| Thanks MichaelThis is what I've used and it works a treat!SELECT DATEPART(d, DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))) AS daysInMonth |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-03-03 : 11:28:10
|
This is slightly simpler:select day(dateadd(mm,datediff(m,-1,getdate()),-1)) as daysInMonth CODO ERGO SUM |
|
|
|
ScottBot
Starting Member
16 Posts |
Posted - 2011-03-03 : 11:53:43
|
| Ah fabulous! thanks again for your help, time and knowledge sharing. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Trim Date