Need query using DateTime function [Resolved]

Posted by Kirthiga under Sql Server on 7/5/2012 | Points: 10 | Views : 1359 | Status : [Member] | Replies : 9
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 in current month and day should be day(DueDate)
For Example:

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

For each month i have to update [InstDueDate] column




Responses

Posted by: Pandians on: 7/6/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Here is the solution!
Declare @GetDate datetime

select @GetDate = GETDATE()

;With Dates
As
(
Select DueDate,Cast(DAY(DueDate) as Varchar) + '-' + Cast(DATENAME(Month,@GetDate) as varchar(3)) + '-' + Cast(YEAR(@GetDate) as varchar) InstDueDate from Sample1
),DateValidate
As
(
Select *, ISDATE(InstDueDate) IsValidDate from Dates
), DaysInMonth
AS
(
Select *,Day(Dateadd(Month,1,Cast(Cast(YEAR(@GetDate) as varchar) + '-' + Cast(MONTH(@GetDate) as varchar) + '-01' as datetime))-1) [Days] from DateValidate
), ValidDate
As
(
Select *, CASE IsvalidDate WHEN 0 THEN Cast(Cast(Year(@GetDate) as varchar) + '-' + Cast(MONTH(@GetDate) as varchar) + '-' + Cast([Days] as varchar) as Datetime) ELSE InstDueDate End [ValidDate] from DaysInMonth
)

Update S Set S.InstDueDate = V.ValidDate From Sample1 S Join ValidDate V
On (S.DueDate = V.DueDate)

Select * from Sample1
Go


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/5/2012 [Member] Starter | Points: 25

Up
0
Down
this is one of the method to get current date.
DateTime dt=DateTime.Now.Date;
txtGrdate.Text=dt.ToShortDateString();
Get this date and update into database




Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/5/2012 [Member] Starter | Points: 25

Up
0
Down
select Getdate() -u can get current date and time

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 7/5/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks for ur reply

I dont want current date.

I need a select query for '2012-07-'+day(DueDate) ==> '2012-07-31'

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 7/5/2012 [Member] Starter | Points: 25

Up
0
Down

string month=("12/07/2012").Substring(0,2);=>12

get month alone separately like this and append into InstDueDate

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 7/5/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Try this...!
Create Table Sample1

(
DueDate DateTime,
InstDueDate DateTime
)
Go

Insert Sample1(DueDate) Values('31-Mar-2004')
Insert Sample1(DueDate) Values('30-May-2008')
Insert Sample1(DueDate) Values('26-Dec-2003')
Insert Sample1(DueDate) Values('25-Apr-2012')
Go

Update Sample1 Set InstDueDate = Cast(DAY(DueDate) as Varchar) + '-' + Cast(DATENAME(Month,getdate()) as varchar(3)) + '-' + Cast(YEAR(getdate()) as varchar)
Go

Select * from Sample1
Go
Result
DueDate	                        InstDueDate

2004-03-31 00:00:00.000 2012-07-31 00:00:00.000
2008-05-30 00:00:00.000 2012-07-30 00:00:00.000
2003-12-26 00:00:00.000 2012-07-26 00:00:00.000
2012-04-25 00:00:00.000 2012-07-25 00:00:00.000


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/5/2012 [Member] Silver | Points: 25

Up
0
Down
@Pandians
Nice solution...!!!
But when are evaluating this in the months that have only 30day say "September'.
The output will be
31-Mar-2004 31-Sep-2012, Which is not a valid date.


Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 7/5/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Yes I agree!

Days has to be validated before converting/casting into another Month!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 7/6/2012 [Member] Starter | Points: 25

Up
0
Down
Thank u Pandian..

But how to arrive this for february month having 29 days.
For 31,30,29 day the date should be '29-Feb-2012'

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response