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)
 Working out First and Last day of the month

Author  Topic 

fezz
Starting Member

12 Posts

Posted - 2010-07-27 : 04:25:35
Hi,

I've been trying to work out the first and last day of the month based on an input of July 2010 for example, finding these dates based on a proper datetime field is easy but I'm having trouble converting July 2010 into a proper datetime value, can anyone help?

Thanks

Pete

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-27 : 04:28:49
[code]
declare @input datetime

select @input = getdate()

select dateadd(month, datediff(month, 0, @input), 0),
dateadd(month, datediff(month, 0, @input)+1, -1)
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

fezz
Starting Member

12 Posts

Posted - 2010-07-27 : 04:31:20
Thanks khtan, but thats the bit i can already do. I need to know how to convert July 2010 so it can be used in the query above.

Thanks

Pete
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-27 : 04:32:56
what is the input ? text ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-27 : 04:34:12
[code]select convert(datetime, 'July 2010')[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

fezz
Starting Member

12 Posts

Posted - 2010-07-27 : 04:39:34
oh that did the job, i was trying to complicate it too much i think :)

Thanks :)

Pete
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 06:38:31
Also make sure to read the examples of the last part
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -