Need query using DateTime function [Resolved]

Posted by Kirthiga under Sql Server on 7/5/2012 | Points: 10 | Views : 2518 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

0	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

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: Pandians on: 7/5/2012 [Member] [MVP] Silver | Points: 25

0	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

0	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

Latest Posts