replace month part of a date - SQL Server Forums

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)

replace month part of a date

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-05-21 : 05:49:21

DECLARE @Current_DT DATETIME, @Month INT

SET @Current_DT =GETDATE()
SET @MOnth=8
SET @Current_DT =CONVERT(DATETIME,REPLACE(CONVERT(VARCHAR,@Current_DT),CONVERT(VARCHAR,DATEPART(MM,@Current_DT)),CONVERT(VARCHAR,@Month)))

I used the above code. but still i get the date as 05/21/2009(Today's date). I want to replace the month of today's date with the number i give.

Thanks in Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 06:10:52

select dateadd(month, @Month - month(@Current_DT), @Current_DT)

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

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-05-21 : 07:15:46

Thanks a lot Khtan.

This is one more solution which i got.

DECLARE @myDate DATETIME
DECLARE @MonthValue INT
SET @myDate = GETDATE()
SELECT @myDate
SET @MonthValue = 3

SET @myDate = CAST(CAST(@MonthValue AS VARCHAR(2))+'/'+ CAST(DATEPART(DD,@myDate)AS VARCHAR(2))+'/'+CAST(DATEPART(YYYY,@myDate)AS VARCHAR(4)) AS DATETIME)
SELECT CONVERT(VARCHAR(10),@myDate,101)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 08:04:53

mine is shorter and easier to understand

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-21 : 11:24:40

quote:
Originally posted by swathigardas

Thanks a lot Khtan.

This is one more solution which i got.

DECLARE @myDate DATETIME
DECLARE @MonthValue INT
SET @myDate = GETDATE()
SELECT @myDate
SET @MonthValue = 3

SET @myDate = CAST(CAST(@MonthValue AS VARCHAR(2))+'/'+ CAST(DATEPART(DD,@myDate)AS VARCHAR(2))+'/'+CAST(DATEPART(YYYY,@myDate)AS VARCHAR(4)) AS DATETIME)
SELECT CONVERT(VARCHAR(10),@myDate,101)

This might work, but it could give you unexpected results if someone changed a server setting from mm/dd/yyyy to dd/mm/yyyy. Khtan's is much more simple and won't fail on a server with a different date setting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources