How to add this code in?? 1

[Cpreston]

I have a view that is using this syntax below. it works fine but I have to change the date manually everytime I want to see new results.

SELECT dbo.Customer.CustomerCode AS [CUST.ID], dbo.Customer.Name AS [CUST.NAME], dbo.CustomerAddress.City AS [CUST.SHORTADDR],
dbo.CustomerAddress.Address1 AS [CUST.TEXT01],
dbo.CustomerAddress.Address2 + ' , ' + dbo.CustomerAddress.Address2 + ' , ' + dbo.CustomerAddress.County AS [CUST.TEXT02],
dbo.JourneySODetail.DeliveryPostCode AS [CUST.POSTCODE], dbo.DeliveryArea.Name AS [CUST.TEXT03], dbo.Customer.CustomerCode AS [CALL.ID],
'D' AS [CALL.TYPE], dbo.JourneySODetail.TotalVolume AS [CALL.USER01], dbo.JourneySODetail.TotalWeight AS [CALL.USER02], CONVERT(varchar,
dbrderHeader.DateRequired, 103) AS [CALL.TEXT01], '08:00' AS [CALL.TEXT02], '17:30' AS [CALL.TEXT03],
REPLACE(REPLACE(dbrderHeader.SpecialInstructions, CHAR(13), ' '), CHAR(10), '') AS [CALL.TEXT04], dbrderHeader.CustomerRef AS [CALL.TEXT05],
dbrderHeader.OrderNumber AS [CALL.TEXT06], 0 AS [CALL.USER03]
FROM dbrderHeader INNER JOIN
dbo.CustomerAddress ON dbrderHeader.DeliveryAddressID = dbo.CustomerAddress.CustomerAddressID INNER JOIN
dbo.JourneyHeader ON dbrderHeader.JourneyID = dbo.JourneyHeader.JourneyID INNER JOIN
dbo.Customer ON dbrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
dbo.DeliveryArea ON dbrderHeader.DeliveryAreaID = dbo.DeliveryArea.DeliveryAreaID INNER JOIN
dbo.JourneySODetail ON dbrderHeader.OrderID = dbo.JourneySODetail.OrderID
WHERE (CONVERT(varchar, dbrderHeader.DateRequired, 103) = '08/04/2016')


I would like to apply something like the below for the dbrderHeader.DateRequired. I have tried to apply it but the more I try I get deeper into issues, could some kind person show me how to add the code in please. I will continue trying in the meantime. Thanks

SELECT FORMAT(

-- dates start on Sunday (1) and go to Saturday (7)
DATEADD(day,

-- number of days to add
CASE DatePart(Weekday,GetDate())
WHEN 1 THEN 1
WHEN 2 THEN 0
WHEN 3 THEN 6
WHEN 4 THEN 5
WHEN 5 THEN 4
WHEN 6 THEN 3
WHEN 7 THEN 2
END,

-- date to add them to
GETDATE()

),

-- the formatting code can use any combination of d, M and y
'dddd dd MMMM yyyy'
)

 

[Olaf Doschke]

This code is not just formatting, it is resulting in the next monday in that format.

Only doing the FORMAT(somedate,'dddd dd MMMMM yyyy') for example yields 'Tuesday 12 April 2016' for today.
This only works in SQL2012 or higher, maybe your database won't support this.

Test this only, not inside any query:

SELECT FORMAT(getdate(),'dddd dd MMMMM yyyy')

Bye, Olaf.

 

[Cpreston]

Hi

Ok I got this

Msg 195, Level 15, State 10, Line 1
'FORMAT' is not a recognized built-in function name.

Sorry the database is on 2008r2 so maybe format will not work. I am using MGMT Studio 2012 so go a bit confused, the server is 2008r2

Basically I am trying to achieve orderheader.daterequired to be the date as follows when the view is run

If Monday I want it to be the required date of Wednesday
If Tuesday to be Thursday
If Wednesday to be Friday
If Thursday to be Monday
If Friday to be Tuesday

The date format must be dd/mm/yyyy

Thanks

 

[Olaf Doschke]

>WHERE (CONVERT(varchar, dbrderHeader.DateRequired, 103) = '08/04/2016')

Do you want to control this format?

The best way to make date comparisons is using the format 'YYYYMMDD', that works regardless of CONVERT or locale settings of Server or Database or even OS.
WHERE dbrderHeader.DateRequired = '20160408'

Or for todays data:
WHERE Cast(dbrderHeader.DateRequired as date) = CAST(GetDate() as date)

Bye, Olaf.

 

[Cpreston]

WHERE (CONVERT(varchar, dbrderHeader.DateRequired, 103) = '08/04/2016')

I have this code already in the view and as I mentioned I have to add the date in manually, so as it is Tuesday I would need to add in Thursday date of 14/04/2016 to get my required information

I do not want to go into the view all the time to do this but would like it to know
If Monday I want it to be the required date of Wednesday
If Tuesday to be Thursday
If Wednesday to be Friday
If Thursday to be Monday
If Friday to be Tuesday


Is this not possible?

Thanks

 

[Olaf Doschke]

In regard of the day change you need the DATEADD part of your previous code, but other values for you always want different days.
One question: Is it always previous or next? I assume previous, but data might also be about futire orders or deliveries or something like that, so please make yourself clear:

If quering on Monday you want data of previous or next Wednesday?

Bye, Olaf.

 

[philhege]

Looks like you want records two business days in the future.

Try
WHERE (CONVERT(varchar, dbrderHeader.DateRequired, 103) = convert(varchar,DATEADD(weekday,2,getdate()),103))

-----------
With business clients like mine, you'd be better off herding cats.

 

[philhege]

Ugh, should run code before I post it.

-----------
With business clients like mine, you'd be better off herding cats.

 

[philhege]

replace the code on the right side of the equation with
convert(varchar,DATEADD(d,CASE DatePart(dw,getdate())
WHEN 1 THEN 1
WHEN 7 THEN 2
WHEN 6 THEN 3
WHEN 5 THEN 4
ELSE 2
END,getdate()),103)

-----------
With business clients like mine, you'd be better off herding cats.

 

[fredericofonseca]

this is most likely related to one of your first posts on this and the use of a calendar table so it is advisable you go and reread it.

so what do you wish the date to be if the date you are processing is on a Thursday (which would equate to Monday on your example above) and the following Monday is a bank holiday? should the date returned to you be that same Monday or should it return Tuesday instead?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

>this is most likely related ...the use of a calendar table
OK, I wasn't part of that discussion, so I'm perhaps out here.

I don't know how the discussion went, but even in 2008R2 you can make use of LEAD to look forward 2 records from a business day calendar record. The simple CASE surely isn't covering holidays, it just can cope with the weekend days, but not be a general solution.

Bye, Olaf.

 

[Cpreston]

Hi

Thanks all for the replys, any code given I cannto try out until tomorrow during the day so will feed back then.

fredericofonseca it may well be related to a calender as you suggest from a previous post. However in this case a required delivery date would be moved to a Tuesday on our system date in the case of bank holidays so would not be part of the issue.

philhege you mention putting the code you give to the right hand side of the equation, where do you mean exactly please.

Again many thanks for the replys very useful I will try them tomorrow

Thanks

 

[Cpreston]

Hi

Ok tried some of the code

philhege I am getting this when running your code

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ','.

This is the code as I have it now and Line 23 is the END, getdate(),103) row. I have tried to change it about but keep getting the same message. Any ideas please.

FROM dbrderHeader INNER JOIN
dbo.CustomerAddress ON dbrderHeader.DeliveryAddressID = dbo.CustomerAddress.CustomerAddressID INNER JOIN
dbo.JourneyHeader ON dbrderHeader.JourneyID = dbo.JourneyHeader.JourneyID INNER JOIN
dbo.Customer ON dbrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
dbo.DeliveryArea ON dbrderHeader.DeliveryAreaID = dbo.DeliveryArea.DeliveryAreaID INNER JOIN
dbo.JourneySODetail ON dbrderHeader.OrderID = dbo.JourneySODetail.OrderID

WHERE (CONVERT(varchar, dbrderHeader.DateRequired, 103) = convert(varchar,DATEADD(d,CASE DatePart(dw,getdate())
WHEN 1 THEN 1
WHEN 7 THEN 2
WHEN 6 THEN 3
WHEN 5 THEN 4
ELSE 2
END, getdate()),103)

I have also tried the row below and it gives me Fridays date which is great, but would prefer it all do the dates like above.

WHERE (CONVERT(varchar, dbrderHeader.DateRequired, 103) = convert(varchar,DATEADD(weekday,2,getdate()),103))

Many thanks

 

[Cpreston]

Hi

Think I have it working, I have changed the code to below and it is bringing in Fridays dates at the moment. I will test through the week as I cannot change the server date. Code is as below, I update when I have tested for a week. Many thanks

FROM dbrderHeader INNER JOIN
dbo.CustomerAddress ON dbrderHeader.DeliveryAddressID = dbo.CustomerAddress.CustomerAddressID INNER JOIN
dbo.JourneyHeader ON dbrderHeader.JourneyID = dbo.JourneyHeader.JourneyID INNER JOIN
dbo.Customer ON dbrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
dbo.DeliveryArea ON dbrderHeader.DeliveryAreaID = dbo.DeliveryArea.DeliveryAreaID INNER JOIN
dbo.JourneySODetail ON dbrderHeader.OrderID = dbo.JourneySODetail.OrderID

WHERE convert(varchar, DATEADD(d,CASE DatePart(dw,getdate())
WHEN 1 THEN 1
WHEN 7 THEN 2
WHEN 6 THEN 3
WHEN 5 THEN 4
ELSE 2
END, getdate()),103) = (CONVERT(varchar, dbrderHeader.DateRequired, 103))

 

[philhege]

Sorry, missed the closing paren you wrapped around your WHERE clause. You should be good to go now.

-----------
With business clients like mine, you'd be better off herding cats.