SQL syntax to give me Mon-Sun data for the previous week

[SachinC]

Hi,
I have an object called: [Visitor].[Arrivaldatetime]. It's in text, hence I am converting it to dd/mm/yyyy.

I have a report that will run at 9am on a Monday morning, but I need data for the previous week - i.e. 00:00:01 from Mon to 23:59:59 to Sunday.

This statement doesn't work that great: [Visitor].[Arrivaldatetime] > CONVERT (varchar, DATEADD(day, - 6, GETDATE()), 101), as it pokes in the following Monday which I don't need.

Help please!

Thanks!

 

[SachinC]

Please can someone help with an answer to this? Thank you.

 

[FormR]

Hi, perhaps..

where 
    cast([Visitor].[Arrivaldatetime] as date) between cast(getdate()-7 as date) and cast(getdate()-1 as date)

 

[SachinC]

That's great, can I be more defensive in the where clause as this will trigger 9am Monday for data from the week before, for example, Monday 6th Dec 9am an email will trigger with xlsx attachments for the period Monday 29th Nov 00:00:01 to Sunday 5th December 2021 23:59:59 - thoughts? Thank you.

 

[FormR]

Hi, in what way does it not already do that?

 

[SachinC]

Ok, just checking! I will give it a go. Thanks.

 

[SachinC]

Hi, perhaps..

where
    cast([Visitor].[Arrivaldatetime] as date) between cast(getdate()-7 as date) and cast(getdate()-1 as date)

Hi,
Quick question for you...

I am using a 3rd party charting tool, my SQL datetime field needs to be formatted to dd/mm/yyyy using the syntax: DATENAME(DAY,[Visitor].[ArrivalDateTime])+' '+DATENAME(MONTH,[Visitor].[ArrivalDateTime])+' '+CAST(YEAR([Visitor].[ArrivalDateTime]) AS NVARCHAR(4))

The issue is that although the object Datatype is Date, and displays 01 December 2021 for example, this field is unsortable. I noticed this when reporting on last week because Mon was 29/11 and Sun was 05/12 and the sorting went pear-shaped.

How can I resolve please? I cannot use the raw [ArrivalDateTime] because I am summing on duration etc...

Thanks!

 

[FormR]

I am using a 3rd party charting tool

Can't you include the arrival date in the query and ignore it in the 3rd party tool or can you not do the sorting in the 3rd party tool?

using the syntax: DATENAME(DAY,[Visitor].[ArrivalDateTime])+' '+DATENAME(MONTH,[Visitor].[ArrivalDateTime])+' '+CAST(YEAR([Visitor].[ArrivalDateTime]) AS NVARCHAR(4))

FWIW - you could probably also achieve the same result with..

format(cast(Visitors.Arrivaldatetime as date),'dd MMMM yyyy')

 

[SachinC]

Thanks - had to modify to this: format(cast([Visitor].[ArrivalDateTime] as date),'dd MMM yyyy') but still does not sort the object. Good try. Help!

 

[Kyle123]

You're converting the date into a string which is why you can't sort it. As a rule, you don't format in SQL you format in the application that calls it, there are a lot of reasons for this - so your charting tool should do the formatting here.

Are you sure that the issue isn't with whatever graphing tool you're using? - I'd be amazed if it wanted strings in the format dd/MMM/yy rather than actual dates