Member Avatar for jovillanuev

Hi Guys,

I have a requirements to backtrack 2 days if the date fall as Monday.
Why? We dont have any transaction from Sunday to Monday at 6:00AM that's why need to backtrack 2 days.
i want to share with you my query. if you have any additional idea to simplify this is very much appreaciated. Thanks.

--Sample DDL
Create table #SampleData(Prodid nvarchar(35), model nvarchar(35), Trandate datetime)
Go
Insert into #SampleData
Select 'TRC003328042','DRDRZR2BLK','2013-08-31 04:30:33.000' union all---Saturday
Select 'TRC003327670','DRDRZR2BLK','2013-08-31 02:40:23.000'union all---Saturday
Select 'TRC003327822','DROIDRZR','2013-08-31 05:17:28.000'union all---Saturday
Select 'TRC003328342','DROIDRZR','2013-08-31 08:10:27.000'union all---Saturday
Select 'TRC003328387','DROIDRZR','2013-08-31 09:22:01.000'union all---Saturday
Select 'TRC003322836','DRDRZRHDBLK','2013-08-30 23:40:19.000'union all---Saturday
Select 'TRC003326586','DROIDBIO2','2013-08-31 05:50:47.000'union all---Saturday
Select 'TRC003325460','DROIDBIO2','2013-08-30 22:36:50.000'union all---Saturday
Select 'TRC003328994','DRDRZR2WHT','2013-09-02 04:33:44.000'union all-- Monday
Select 'TRC003328998','DRDRZR2WHT','2013-09-02 06:37:13.000'-- Monday
Select 'TRC003328994','DRDRZR2WHT','2013-09-03 06:40:12.000'-- Tuesday

--Created SSRS Parameter
Declare @FromDate datetime, @ToDate datetime, @TodayName nvarchar(10)
@Fromtime nvarchar(6), @Totime nvarchar(6) ,@timezoneOffset int
set @FromDate='9/2/2013' --Monday
set @ToDate='9/3/2013'
Set @Fromtime='6:00 AM'
Set @Totime='6:00 AM'
set @timezoneOffset=8
Set @TodayName='Monday' --based on @FromDate(9.2.13)

--create dataset for todayname
Select DateName(dw,getdate())--Result set is monday

-Dataset Properties->Parameter
Parameter name--------Parameter value
@PF
Edited by pritaeas because: Added markdown.
  • 2 Contributors
  • 1 Reply
  • 231 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by ChrisHunter

Dani AI

Generated

asked for a way to "backtrack 2 days if the date falls on Monday." is right to point toward DATEADD — the simplest, most reliable pattern is: build a single datetime from your date + time parameters, test whether that datetime falls on a Monday in a way that does not depend on session language/DATEFIRST, and then DATEADD(day, -2, ...) when it does.

-- combine date + time into one datetime
DECLARE @FromDT datetime;
SET @FromDT = CAST(CONVERT(char(10), @FromDate, 120) + ' ' + @Fromtime AS datetime);

-- Monday check independent of SET DATEFIRST
IF ((DATEPART(weekday, @FromDT) + @@DATEFIRST - 2) % 7) = 0
    SET @FromDT = DATEADD(day, -2, @FromDT);

-- apply timezone offset (if @timezoneOffset is hours)
SET @FromDT = DATEADD(minute, -@timezoneOffset * 60, @FromDT);

Notes and cautions: the weekday test above maps Monday reliably regardless of DATEFIRST or language. If you only want to backtrack when the requested time is exactly 06:00, add a time check (e.g., CONVERT(time,@FromDT) = '06:00:00'). Prefer time/datetime2 types for precision. If you need correct daylight‑saving handling or stored offsets, use UTC storage and convert with AT TIME ZONE (SQL Server 2016+) rather than a fixed hour offset. Test edge cases (DST transitions, midnight boundaries) to confirm the behavior matches your business rule.

Member Avatar for ChrisHunter

Theres a build in DATEADD() function in SQL Server (2005 at least), which lets you add or subtract seconds, minutes, hours, days, months or years to a datetime value.

Have a look at this link and it will tell you everything you need to know.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.