how to calulate next monday date value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to calulate next monday date value

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2010-05-23 : 17:21:10

i am trying to calculate the date available in flydate column (is a datetime data type column) to next monday, so presently i am doing this below way(it is working fine but seems to be i am doing tooo much here), is there any efficient & simple query for this please.....

select
Flight.flyDate,
CASE datepart(weekday, Flight.flyDate)
WHEN 1 THEN upper(substring(CONVERT(varchar(50), Flight.flyDate), 1, 3)) + '/' + substring(CAST(year(Flight.flyDate) AS varchar(4)), 3, 2)
WHEN 2 THEN upper(substring(CONVERT(varchar(50), DATEADD(day, 6, Flight.flyDate)), 1, 3)) + '/' + substring(CAST(year(DATEADD(day, 6, Flight.flyDate)) AS varchar(4)), 3, 2)
WHEN 3 THEN upper(substring(CONVERT(varchar(50), DATEADD(day, 5, Flight.flyDate)), 1, 3)) + '/' + substring(CAST(year(DATEADD(day, 5, Flight.flyDate)) AS varchar(4)), 3, 2)
WHEN 4 THEN upper(substring(CONVERT(varchar(50), DATEADD(day, 4, Flight.flyDate)), 1, 3)) + '/' + substring(CAST(year(DATEADD(day, 4, Flight.flyDate)) AS varchar(4)), 3, 2)
WHEN 5 THEN upper(substring(CONVERT(varchar(50), DATEADD(day, 3, Flight.flyDate)), 1, 3)) + '/' + substring(CAST(year(DATEADD(day, 3, Flight.flyDate)) AS varchar(4)), 3, 2)
WHEN 6 THEN upper(substring(CONVERT(varchar(50), DATEADD(day, 2, Flight.flyDate)), 1, 3)) + '/' + substring(CAST(year(DATEADD(day, 2, Flight.flyDate)) AS varchar(4)), 3, 2)
WHEN 7 THEN upper(substring(CONVERT(varchar(50), DATEADD(day, 1, Flight.flyDate)), 1, 3)) + '/' + substring(CAST(year(DATEADD(day, 1, Flight.flyDate)) AS varchar(4)), 3, 2)
ELSE ' '
END AS FLSERVICE
FROM Flight
where flight.customertype <> 'kid'

is there anyway to simply statement ?

Please,.......
Thanks in advance

dhani

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-23 : 18:47:03

Use this:


SELECT F.flyDate, DATEADD(WEEK, DATEDIFF(WEEK, '20100104', F.flyDate), '20100111') AS FLSERVICE
  FROM Flight AS F
 WHERE F.customertype <> 'kid'

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-24 : 14:10:52

I fixed the typo in my previous reply and used '20100111' instead of '20100104'.

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-05-25 : 01:12:24

Please Chk This Query will return the next week start date.

Select
DATEADD(DD, 1 - DATEPART(DW, getdate()), getdate()) AS StartOftheWeek,
DATEADD(DD,7,DATEADD(DD, 1 - DATEPART(DW, getdate()), getdate())) as NextWeekStartDate

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources