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 |
|
theboyholty
Posting Yak Master
226 Posts |
 Posted - 2012-08-23 : 11:45:05
|
| I have a field in a database called HomeInsRenewMonth, which is populated from a drop down list on a website and the choice is for the month your home insurance is due for renewal. Its a VARCHAR field and the month name comes out in full. i.e.'January',February','March'I need to export this information but as a DATETIME and always referring to the 1st date of the next occurrance of the given month.For example:If the selection is October, I need to output '2012-10-01 00:00:00.000' If its December, the output should be '2012-12-01 00:00:00.000' If March, I want to output '2013-03-01 00:00:00.000'If its August, it should be the current month i.e. '2012-08-01 00:00:00.000'I know I can use DATEPART to get the month name from a date, but can this be done the other way? I doubt it. Failing that, I'd appreciate some input as this is taking far too long.Thanks---------------------------------------------------------------------------------http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-23 : 12:00:09
|
| declare @s varchar(20) select @s = 'January'declare @d datetimeselect @d = '1 ' + @s + ' ' + convert(varchar(4),getdate(),112)if @d < getdate() select @d = dateadd(mm,12,@d)select @d==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2012-08-23 : 12:33:55
|
| You're a very clever man Nigel. Thanks very much. Saved me a world of pain there.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk A Bury FC supporters website and forum |
|
|
|
|
|
|
|
|
A Tricky Bugger