DateAdd() Returning incorrect date

[Salbrox]

Hi folks,

A strange issue i'm having here. I have a view with a chain of dateadd()'s in it.

They are calculating fine up to a point where i have a dateadd which should add 3 days to the date but instead it is adding 69 days. This makes no sense to me.

DATEADD(day , 3 , [Change Order Issued Forecast])

I have built this query based on a large Access query which works fine.

[Change Order Issued Forecast] is also a dateadd field which in turn is calculated from another dateadd field which is calculated from another and another and so on (about 10 dateadd fields calculating from each other).

Just wondering what could cause this and how could I possibly solve the issue.

I'm on SQL Server 2005 using SQL Server 2008 Management Studio.

Any help is super appreciated!

 

[Salbrox]

This is the code:

SELECT     
		MW_PSR_ID, 
		[Issue Technical Package Planned], 
		[Issue Technical Package Planned] AS [Issue Technical Package Forecast], 
		[Issue Technical Package Actual], 
		DATEADD(day, - 14, [Quotation Received From Vendor Planned]) AS [RFQ Issued to Vendor Planned], 
		CASE WHEN [RFQ Issued to Vendor Actual] IS NULL THEN DATEADD(day, 7, [Issue Technical Package Forecast]) ELSE [RFQ Issued to Vendor Actual] END AS [RFQ Issued to Vendor Forecast], 
	    [RFQ Issued to Vendor Actual], 
		DATEADD(day, - 7, [Issue for Technical Analysis Planned]) AS [Quotation Received From Vendor Planned], 
	    CASE WHEN [Quotation Received From Vendor Actual] IS NULL THEN DATEADD(day, 14, [RFQ Issued to Vendor Forecast]) ELSE [Quotation Received From Vendor Actual] END AS [Quotation Received From Vendor Forecast], [Quotation Received From Vendor Actual], DATEADD(day, - 7, 
	    [Technical Analysis Completed Planned]) AS [Issue for Technical Analysis Planned], 
		[Issue for Technical Analysis Forecast], 
		[Issue for Technical Analysis Actual], 
	    DATEADD(day, - 4, [CBA Completed Planned]) AS [Technical Analysis Completed Planned], 
		CASE WHEN [Technical Analysis Completed Actual] IS NULL THEN DATEADD(day, 7, [Issue for Technical Analysis Forecast]) ELSE [Technical Analysis Completed Actual] END AS [Technical Analysis Completed Forecast], 
	    [Technical Analysis Completed Actual], 
		DATEADD(day, - 3, [Entitlement Submitted Planned]) AS [CBA Completed Planned], 
		CASE WHEN [CBA Completed Actual] IS NULL THEN DATEADD(day, 4, [Technical Analysis Completed Forecast]) ELSE [CBA Completed Actual] END AS [CBA Completed Forecast], 
		[CBA Completed Actual], 
	    DATEADD(day, - 2, [Entitlement Approved Planned]) AS [Entitlement Submitted Planned], 
		CASE WHEN [Entitlement Submitted Actual] IS NULL THEN DATEADD(day, 3, [CBA Completed Forecast]) ELSE [Entitlement Submitted Actual] END AS [Entitlement Submitted Forecast], 
		[Entitlement Submitted Actual], 
		DATEADD(day, - 3, [Change Proposal Submitted Planned]) AS [Entitlement Approved Planned], 
		CASE WHEN [Entitlement Approved Actual] IS NULL THEN DATEADD(day, 2, [Entitlement Submitted Forecast]) ELSE [Entitlement Approved Actual] END AS [Entitlement Approved Forecast], 
		[Entitlement Approved Actual], DATEADD(day, - 3, [Change Proposal Approved Planned]) AS [Change Proposal Submitted Planned], 
		CASE WHEN [Change Proposal Submitted Actual] IS NULL THEN DATEADD(day, 3, [Entitlement Approved Forecast]) ELSE [Change Proposal Submitted Actual] END AS [Change Proposal Submitted Forecast], 
		[Change Proposal Submitted Actual], 
	    DATEADD(day, - 3, [Change Order Issued Planned]) AS [Change Proposal Approved Planned], 
		CASE WHEN [Change Proposal Approved Actual] IS NULL THEN DATEADD(day, 3, [Change Proposal Submitted Forecast]) ELSE [Change Proposal Approved Actual] END AS [Change Proposal Approved Forecast], 
	    [Change Proposal Approved Actual], 
		DATEADD(day, - 7, [PO Issued Planned]) AS [Change Order Issued Planned], 
		CASE WHEN [Change Order Issued Actual] IS NULL THEN DATEADD(day, 3, [Change Proposal Approved Forecast]) ELSE [Change Order Issued Actual] END AS [Change Order Issued Forecast], 
	    [Change Order Issued Actual], 
		DATEADD(day, - 7, [Submittal Issued Planned]) AS [PO Issued Planned], 
		CASE WHEN [PO Issued Actual] IS NULL THEN DATEADD(day, 3, [Change Order Issued Forecast]) ELSE [PO Issued Actual] END AS [PO Issued Forecast], 
		[PO Issued Actual], 
		DATEADD(day, - 14, [Submittal Approved Planned]) AS [Submittal Issued Planned], 
		CASE WHEN [Submittal Issued Actual] IS NULL THEN DATEADD(day, 7, [PO Issued Forecast]) ELSE [Submittal Issued Actual] END AS [Submittal Issued Forecast], 
		[Submittal Issued Actual], 
		DATEADD(day, [Lead Time] * 7, FND) AS [Submittal Approved Planned], 
	    CASE WHEN [Submittal Approved Actual] IS NULL THEN DATEADD(day, 14, [Submittal Issued Forecast]) ELSE [Submittal Approved Actual] END AS [Submittal Approved Forecast], 
		[Submittal Approved Actual], 
		[Lead Time], 
	    [Lead Time] * 7 + [Submittal Approved Forecast] AS FORECAST_DELIVERY_DATE, 
		FND, 
		CASE WHEN [FND] IS NULL THEN '0' ELSE datediff(day, [FND], 
	    [FORECAST_DELIVERY_DATE]) / 7 END AS FLOAT
FROM         
		dbo.Q_PSR_P6_FND

 

[Mihail]

The theory:
X=DateAdd(Interval As String, Number As Double, Date)

So, your code should be:
X = DateAdd("d", 3, [Change Order Issued Forecast])

assuming that [Change Order Issued Forecast] is a field with Date as data type.

Note:
Your day is interpreted as a variable and, unless this variable is as string type and hold the value "d" the DateAdd functions has not the properly parameters.

One more thing:
Use Option Explicit in your VBA editor.

Good luck !

 

[Salbrox]

Hmm, im not using any VBA here!

 

[Salbrox]

I have removed all the unnecessary code below. Maybe someone could try and re-create my issue.

Create a table in SQL Server called dbo.Q_PSR_P6_FND, add a field called MW_PSR_ID as your ID field and one called [Issue for Technical Analysis Forecast] and enter '2012-08-09 00:00:00.000' then run this query:

SELECT     MW_PSR_ID, [Issue for Technical Analysis Forecast], DATEADD(d, 7, [Issue for Technical Analysis Forecast]) AS [Technical Analysis Completed Forecast], DATEADD(d,
                       4, [Technical Analysis Completed Forecast]) AS [CBA Completed Forecast], DATEADD(d, 3, [CBA Completed Forecast]) AS [Entitlement Submitted Forecast], DATEADD(d,
                       2, [Entitlement Submitted Forecast]) AS [Entitlement Approved Forecast], DATEADD(d, 3, [Entitlement Approved Forecast]) AS [Change Proposal Submitted Forecast], 
                      DATEADD(d, 3, [Change Proposal Submitted Forecast]) AS [Change Proposal Approved Forecast], DATEADD(d, 3, [Change Proposal Approved Forecast]) 
                      AS [Change Order Issued Forecast], DATEADD(d, 3, [Change Order Issued Forecast]) AS [PO Issued Forecast], DATEADD(d, 7, [PO Issued Forecast]) 
                      AS [Submittal Issued Forecast], DATEADD(d, 14, [Submittal Issued Forecast]) AS [Submittal Approved Forecast]
FROM         dbo.Q_PSR_P6_FND

Whats happening for me is it stops working at the PO Issue Forecast column. It gives an incorrect result and the following columns are null.

 

[Mihail]

As I am sure you understand, why I don't read your second post . For me is in Mandarin language.
But I've answered to the first one.
And is not about VBA. is about the using of DateAdd function.

Now, after I read your last post (#5) I can say that I'm pretty sure that, if you will use quotes around the d , as this "d" inside the DATEADD function in your SQL, all will be OK.

 

[Salbrox]

As I am sure you understand, why I don't read your second post . For me is in Mandarin language.
But I've answered to the first one.
And is not about VBA. is about the using of DateAdd function.

Now, after I read your last post (#5) I can say that I'm pretty sure that, if you will use quotes around the d , as this "d" inside the DATEADD function in your SQL, all will be OK.

SQL Server does not allow me to do this. If i put in "d" it automatically changes it to d if i put in 'd' it gives me an error message.

 

[pbaldy]

Mihail, note the forum this is in. The syntax is correct in SQL Server.

Salbrox, not sure why this is happening, but perhaps the cascading is the problem? Is it possible to leverage all values off the first, instead of cascading?

 

[Salbrox]

I have tried doing it outside the View builder by building a query without the graphical view designer. It allowed the " marks but it didn't fix the issue

 

[Salbrox]

Mihail, note the forum this is in. The syntax is correct in SQL Server.

Salbrox, not sure why this is happening, but perhaps the cascading is the problem? Is it possible to leverage all values off the first, instead of cascading?

Hi paul,

No i dont think i can do this as in the full query (as seen above, probably pretty sloppy but hey) there is CASE THEN ELSE statements which base themselves off of the previous [...Forecast] date field. I'm very surprised that this cascading works fine in Access (which i'm converting from) but not SQL Server.

 

[Mihail]

I've not worked with SQL servers. Maybe this is a bug, maybe other syntax is required...

Try to use double quotes: ""d"" or even three """d"""
Of course, it is only a try. I think that SQL is a string and need something to understand that d is between quotes.
Like in this:
dim X As String
X= "Here Mihail is not between quotes and here ""Mihail"" is inside the quotes"

I'm really sorry, but I can't help you anymore.

 

[Salbrox]

No problem Mihail,

Thank you for trying anyway!

 

[Mihail]

Mihail, note the forum this is in. The syntax is correct in SQL Server.

Unfortunately you have posted this while I'm write my last post.

 

[pbaldy]

Never tried to cascade dates like that, but I'll try to put together a test when things slow down later.

 

[Salbrox]

Thats fantastic thanks Paul.

 

[Salbrox]

I have tried what you said and i was wrong. It should indeed work. But now i'm getting wrong dates from Entitlement Submitted Forecast onwards! it really does not make sense!

Edit:

Actually thats incorrect. I have been checking it (my brains are a bit fried at this stage) and its not calculating how I need it to.

 

[SQL_Hell]

hi there

I think it is because you are deriving [Change Order Issued Forecast] further up the query, so that column doesn't exist until runtime.

So your case statement is thinking [Change Order Issued Forecast] is NULL.

If you use a derived table query you will be able reference this column properly.

Strange that syntax checker didn't pick this up

 

[Salbrox]

I think that will work! Never thought of using a derived table.

I'll try that in the morning and let you know how it works!

 

[Salbrox]

Still haven't had the chance to test this guys. Will let you know when i do.