How to get a new date by considering the existing date

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,
Iam having a table with column [DueDate] with DateTime datatype with some values.
I want a new column [InstDueDate] with DateTime datatype,
the value of [InstDueDate] should be [day(DueDate)-Jul-2012]
(ie)[day(DueDate)]-[Current Month]-[Current year]

For Example:

<br />
DueDate InstDueDate<br />
31-Mar-2004 31-Jul-2012<br />
30-May-2008 30-Jul-2012<br />
26-Dec-2003 26-Jul-2012<br />
25-Apr-2012 25-Jul-2012

For each month i have to update [InstDueDate] column.
For the month having 30 days we have to consider the [DueDate] column
for the month of september [31-Mar-2004] should be [30-Sep-2012]
because 31 does not exist in september month.

Posted 27-Jul-12 19:38pm

kirthiga S

Updated 27-Jul-12 19:39pm

v2

Add a Solution

Comments

Santhosh Kumar Jayaraman 28-Jul-12 3:04am

hi krithiga,So is that working?

kirthiga S 28-Jul-12 4:55am

Ya its working..

3 solutions

Solution 2

I need a update query for this scenario

Posted 27-Jul-12 20:08pm

kirthiga S

Comments

barneyman 28-Jul-12 2:15am

I assume that was an response to my answer?

As I suggested, have a look at that link, and have a go at it, and if you get stuck, come back and show us how far you've got ...

We're not paid to be on this site, and while I am more than happy to share any knowledge I have, I am not prepared to do someone else's work

Solution 1

have a look at DATEDIFF and DATEADD[^]

Posted 27-Jul-12 20:06pm

barneyman

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-07-27T20:34:00

hi,

Please check the below one.

SQL

DECLARE @Month nvarchar(2)
DECLARE @Year nvarchar(4)
Declare @MaxDay int

SET @Month = '7'
SET @Year = '2012'
Select @MaxDay=Day(DateAdd(d, -1, DateAdd(m, 1, @Year+ '-' + (@Month) + '-1 23:59:59.998')))


select  Case when DAY(Duedate)<=@MaxDay Then Cast(DAY(Duedate) as varchar) +'-'+  Substring(DATENAME(m, '1900-' + CAST(@Month as varchar(2)) + '-1'),1,3) +'-'+@Year
else  Cast(@MaxDay as varchar) + +'-'+Substring(DATENAME(m, '1900-' + CAST(@Month as varchar(2)) + '-1'),1,3) +'-'+@Year

  end from datemagics

here my table name is datemagics and Duedate is my column. You can replace with your table and column name