Update the list of dates to have the same day

I have this in my table

TempTable
Id    Date
 1开发者_开发问答    1-15-2010
 2    2-14-2010
 3    3-14-2010
 4    4-15-2010

i would like to change every record so that they have all same day, that is the 15th like this

TempTable
Id    Date
 1    1-15-2010
 2    2-15-2010 <--change to 15
 3    3-15-2010 <--change to 15
 4    4-15-2010

what if i like on the 30th? the records should be

TempTable
Id    Date
 1    1-30-2010
 2    2-28-2010 <--change to 28 because feb has 28 days only
 3    3-30-2010 <--change to 30
 4    4-30-2010

thanks

You can play some fun tricks with DATEADD/DATEDIFF:

create table T (
    ID int not null,
    DT date not null
)
insert into T (ID,DT)
select 1,'20100115' union all
select 2,'20100214' union all
select 3,'20100314' union all
select 4,'20100415'

SELECT ID,DATEADD(month,DATEDIFF(month,'20100101',DT),'20100115')
from T

SELECT ID,DATEADD(month,DATEDIFF(month,'20100101',DT),'20100130')
from T

Results:

ID          
----------- -----------------------
1           2010-01-15 00:00:00.000
2           2010-02-15 00:00:00.000
3           2010-03-15 00:00:00.000
4           2010-04-15 00:00:00.000

ID          
----------- -----------------------
1           2010-01-30 00:00:00.000
2           2010-02-28 00:00:00.000
3           2010-03-30 00:00:00.000
4           2010-04-30 00:00:00.000

Basically, in the DATEADD/DATEDIFF, you specify the same component to both (i.e. month). Then, the second date constant (i.e. '20100130') specifies the "offset" you wish to apply from the first date (i.e. '20100101'), which will "overwrite" the portion of the date your not keeping. My usual example is when wishing to remove the time portion from a datetime value:

SELECT DATEADD(day,DATEDIFF(day,'20010101',<date column>),'20100101')

You can also try something like

UPDATE TempTable
SET [Date] = DATEADD(dd,15-day([Date]), DATEDIFF(dd,0,[Date]))

We have a function that calculates the first day of a month, so I just addepted it to calculate the 15 instead...