If you want "7" as the day, start at January 7th, 1900,
and move ahead by full months to the 7th of the
correct month.
If @datetime is your current datetime value, and @newDay
is the day you want (as an integer), this expression will be the
current datetime shifted to the day you want, within the same
month:
This uses the fact that 0 is implicitly converted to January 1, 1900,
and @newDay-1 is January the "@newDay-th", 1900.
declare @datetime datetime
set @datetime = getdate()
declare @newDay int
set @newDay = 7
select
@datetime,
dateadd(month,datediff(month,'19000101',@datetime),@newDay-1)
If @newDay is greater than the number of days in @datetime's month,
you'll end up with the last valid day in @datetime's month.
Steve Kass
Drew University
PML wrote:
Hi,
I am trying to use an update command to set the day of a date to a certain
number. For example, if the date is 1/4/06, I might want to set day(date) to
'7'. So it turns into 1/7/06. I wasn't sure if there was a way to do this
and I keep getting errors when I try it.
Re: Spalten alternativ ansprechen ...DECLARE @Monat INT ... '01' AS DATETIME)...case when DATUM between '...' ...then UMSATZ as ... (microsoft.public.de.access.clientserver)
Re: a view based on stored procedure ...DECLARE @Week_1 DATETIME... DECLARE @CubeTime DATETIME... INSERT INTO WrkAvailPlanHours... (microsoft.public.sqlserver.programming)
Re: Language problem ...@datum datetime... > Then in application I have settings of my connection: ... > everything else doesn't because all other functions are set to slovenian.... > DECLARE @dt DATETIME,@datumMesec dateTime,@datumTedenZ datetime,@datumTedenK ... (microsoft.public.sqlserver.programming)
RE: User Defined Function Help ...@startDateTime datetime = null, ...DECLARE @selectFromList varchar ... SET @subservice = RTRIM ...EXEC... (microsoft.public.sqlserver.programming)
