How to Rectify the month Issue [Resolved]

Posted by Jayakumars under Sql Server on 6/9/2015 | Points: 10 | Views : 445 | Status : [Member] [MVP] | Replies : 5
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


Responses

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

Up
0
Down

Resolved
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

Up
0
Down

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

Up
0
Down
--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

Up
0
Down
/* 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

Up
0
Down
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

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

Login to post response