How to Rectify the month Issue [Resolved]

Posted by Jayakumars under Sql Server on 6/9/2015 | Points: 10 | Views : 1482 | Status : [Member] [MVP] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi
Bandi
I need 2 separate questions here.

1.I have give Starting Date and Ending Date Particular month
But I need shown the DayName Like This

for ex:-- from date :- 01-06-2015 To Date : 30-06-2015

Date DayName
01-06-2015 Monday
Continues
30-06-2015 Tuesday

2. When I select the monthname from my ddl i shown the query

i need output like this based on i select monthname

Date DayName
01-06-2015 Monday
Continues
30-06-2015 Tuesday

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 6/9/2015 [Member] [MVP] Platinum | Points: 50

0	the error is due to the server/database setting of DATETIME format.... You have date format as Month-Day-Year... To resolve above error, SET DATEFORMAT DMY Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 6/9/2015 [Member] [MVP] Platinum | Points: 25

0	DECLARE @date_from datetime, @date_to datetime DECLARE @Month int ,@Year int SET @Month = 2 -- For current month, SELECT MONTH(GETDATE()) SET @Year = 2004 --For current year, SELECT YEAR(GETDATE()) -- If you want to pass @fromDate and #ToDate directly, uncomment below part /* SET @date_from = '01-06-2015' SET @date_to = '30-06-2015' / IF @date_from IS NULL and @date_to IS NULL BEGIN select @date_from = DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /First/ select @date_to = DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /Last/ END ;with dates as( select @date_from as dt union all select DATEADD(d,1,dt) from dates where dt<@date_to ) select dt [Date], DATENAME(WEEKDAY, dt) [DayName] from dates Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator


DECLARE @date_from datetime, @date_to datetime
DECLARE @Month int ,@Year int

SET @Month = 2 -- For current month, SELECT MONTH(GETDATE())
SET @Year = 2004 --For current year,  SELECT YEAR(GETDATE())


-- If you want to pass @fromDate and #ToDate directly, uncomment below part
/*
SET @date_from = '01-06-2015'
SET @date_to = '30-06-2015'
*/

IF @date_from IS NULL and @date_to IS NULL 
BEGIN
	select @date_from = DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/
	select @date_to = DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/
END
;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select dt [Date], DATENAME(WEEKDAY, dt) [DayName] from dates

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Bandi on: 6/9/2015 [Member] [MVP] Platinum | Points: 25

0	--2. When I select the monthname from my ddl i shown the query /* i need output like this based on i select monthname Date DayName 01-06-2015 Monday Continues 30-06-2015 Tuesday / DECLARE @date_from datetime, @date_to datetime DECLARE @Month int ,@Year int SET @Month = 2 -- For current month, SELECT MONTH(GETDATE()) SET @Year = 2004 --For current year, SELECT YEAR(GETDATE()) IF @date_from IS NULL and @date_to IS NULL BEGIN select @date_from = DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /First/ select @date_to = DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /Last/ END ;with dates as( select @date_from as dt union all select DATEADD(d,1,dt) from dates where dt<@date_to ) select dt [Date], DATENAME(WEEKDAY, dt) [DayName] from dates GO Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

--2. When I select the monthname from my ddl i shown the query
/*
i need output like this based on i select monthname

Date DayName
01-06-2015 Monday
Continues
30-06-2015 Tuesday
*/

DECLARE @date_from datetime, @date_to datetime
DECLARE @Month int ,@Year int
SET @Month = 2 -- For current month, SELECT MONTH(GETDATE())
SET @Year = 2004 --For current year,  SELECT YEAR(GETDATE())
 
IF @date_from IS NULL and @date_to IS NULL 
BEGIN
	select @date_from = DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/
	select @date_to = DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/
END

;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select dt [Date], DATENAME(WEEKDAY, dt) [DayName] from dates 

GO

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Bandi on: 6/9/2015 [Member] [MVP] Platinum | Points: 25

0	/* 1.I have give Starting Date and Ending Date Particular month But I need shown the DayName Like This for ex:-- from date :- 01-06-2015 To Date : 30-06-2015 Date DayName 01-06-2015 Monday Continues 30-06-2015 Tuesday / DECLARE @date_from datetime, @date_to datetime SET @date_from = '01-06-2015' SET @date_to = '30-06-2015' ;with dates as( select @date_from as dt union all select DATEADD(d,1,dt) from dates where dt<@date_to ) select dt [Date], DATENAME(WEEKDAY, dt) [DayName] from dates Mark This Response as Answer* -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

/* 1.I have give Starting Date and Ending Date Particular month
But I need shown the DayName Like This

for ex:-- from date :- 01-06-2015 To Date : 30-06-2015

Date DayName
01-06-2015 Monday
Continues
30-06-2015 Tuesday
*/

DECLARE @date_from datetime, @date_to datetime
SET @date_from = '01-06-2015'
SET @date_to = '30-06-2015'
;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select dt [Date], DATENAME(WEEKDAY, dt) [DayName] from dates

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Jayakumars on: 6/9/2015 [Member] [MVP] Bronze | Points: 25

0	Hi Bandi From date and to Date Query Not working error this The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. may be 30-06-2015 error comes dd/mm/yyyy format correct i think can you solve and repost this Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Hi
Bandi

From date and to Date Query Not working

error this

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

may be 30-06-2015 error comes dd/mm/yyyy format correct i think

can you solve and repost this

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

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

Latest Posts