I have a datetime column in my database and I want to change just the month part of it. My application receives an integer that represents the new month value that should be set. I’m wondering what’s the best approach here - should I handle this directly in my SQL query or would it be better to process this in my C# web service code before sending it to the database? I’m working with SQL Server 2005 and looking for the most efficient way to accomplish this task.
u could also consider using CONVERT to just change the month part. but the DATEADD and DATEDIFF method is def the way to go. easier to manage directly in SQL, no need to bring it into C#.
interesting challenge! what happens if ur original date is january 31st and u change it to feburary? does your app handle edge cases where the day doesn’t exist in the new month? how r u planning to deal with that?
For SQL Server 2005, handle this directly in SQL using DATEPART and DATETIME construction. Extract the day and year from your existing datetime, then combine them with your new month like this: CAST(CAST(@newMonth AS VARCHAR) + ‘/’ + CAST(DAY(your_datetime_column) AS VARCHAR) + ‘/’ + CAST(YEAR(your_datetime_column) AS VARCHAR) AS DATETIME). Keep the logic in the database where it belongs - no need for round trips to your application. You’ll get way better performance doing it in SQL instead of pulling data into C# to manipulate it, especially with multiple records.