How to convert value to specific date?

问题:

I need a sql script that can convert value to currents months date, that for example if i pass 26 then it will display 03-26-2011 or convert the 26 to system date of current month and current year and so on.

回答1:

dateadd(month,datediff(month,0,getdate()),0)-1 + 26

The +26 at the end is what makes it the 26th of current month

As a function

create function dbo.DateForDayOfCurrentMonth(@daynum int)
returns datetime
as
begin
return dateadd(month,datediff(month,0,getdate()),0)-1 + @daynum
end
GO

Example usages:

select dbo.DateForDayOfCurrentMonth(26);

select dbo.DateForDayOfCurrentMonth(numbercolumn), ...
from tbl

回答2:

declare @DayOfMonth int = 26
select dateadd(d, @DayOfMonth-1, dateadd(month, datediff(month, 0, getdate()), 0))