I am working on a stored procedure that requires saving the results of
several DATEADD calculations to variables. The problem I am having is
illustrated by the following code:
----
declare @.LastOfRecMo datetime
declare @.FirstOfNextMo datetime
declare @.LastOfNextMo datetime
declare @.AprilFirst datetime
select @.AprilFirst = '4/1/2005'
select @.LastOfRecMo = '3/31/2005'
select @.FirstOfNextMo = DATEADD(d, 1, @.LastOfRecMo)
select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.AprilFirst))
select LastOfRecMo = @.LastOfRecMo,
FirstOfNextMo = @.FirstOfNextMo,
FirstOfNextMo2 = DATEADD(d, 1, @.LastOfRecMo),
LastOfNextMo = @.LastOfNextMo
----
I would expect the @.FirstOfNextMo variable to be set to '4/1/2005', but as
you can see by running the above code, the result, returned as FirstOfNextMo
,
is always NULL, although FirstOfNextMo2 returns the correct date using the
identical calculation. Interestingly, @.LastOfNextMo works correctly with a
much more complex DATEADD calculation.
What's going on here?Sheldon Penner wrote:
> I am working on a stored procedure that requires saving the results of
> several DATEADD calculations to variables. The problem I am having is
> illustrated by the following code:
> ----
> declare @.LastOfRecMo datetime
> declare @.FirstOfNextMo datetime
> declare @.LastOfNextMo datetime
> declare @.AprilFirst datetime
> select @.AprilFirst = '4/1/2005'
> select @.LastOfRecMo = '3/31/2005'
> select @.FirstOfNextMo = DATEADD(d, 1, @.LastOfRecMo)
> select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.AprilFirst))
> select LastOfRecMo = @.LastOfRecMo,
> FirstOfNextMo = @.FirstOfNextMo,
> FirstOfNextMo2 = DATEADD(d, 1, @.LastOfRecMo),
> LastOfNextMo = @.LastOfNextMo
> ----
> I would expect the @.FirstOfNextMo variable to be set to '4/1/2005',
> but as you can see by running the above code, the result, returned as
> FirstOfNextMo, is always NULL, although FirstOfNextMo2 returns the
> correct date using the identical calculation. Interestingly,
> @.LastOfNextMo works correctly with a much more complex DATEADD
> calculation.
> What's going on here?
First thing is the date format you are using is not portable. The only
portable formats are:
yyyymmdd
yyyy-mm-ddThh:mm:ss.mmm(no spaces)
But your code works fine for me with the bad date format. Try changing
the date format and see what you get.
David Gugick
Imceda Software
www.imceda.com|||I'm baffled! I worked on this problem for hours yesterday, restarted the SQ
L
server, rebooted the computer, and could not get the code to run properly.
This morning, after reading your post, I tried again and the problem has gon
e
away. Thank you for your response.
"David Gugick" wrote:
> Sheldon Penner wrote:
> First thing is the date format you are using is not portable. The only
> portable formats are:
> yyyymmdd
> yyyy-mm-ddThh:mm:ss.mmm(no spaces)
> But your code works fine for me with the bad date format. Try changing
> the date format and see what you get.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I thought the problem was solved, but it's not. DATEADD behaves erratically
when given a variable as an argument. The code I sent you yesterday
inexplicably runs correctly now, but the following example, closer to what I
am actually using, does not:
----
declare @.ReceiverDate datetime
declare @.FirstOfRecMo datetime
declare @.LastOfRecMo datetime
declare @.FirstOfNextMo datetime
declare @.LastOfNextMo datetime
-- Receiver Date is 3/27/2005
set @.ReceiverDate = '20050327'
-- First Day of Receiver Month
select @.FirstOfRecMo = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0'
+ cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
select myVar = @.FirstOfRecMo,
calc = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0' +
cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
-- First Day of Month after Receiver Month
select @.FirstOfNextMo = DATEADD(m, 1, @.FirstOfRecMo)
select myVar = @.FirstOfNextMo,
calc = DATEADD(m, 1, @.FirstOfRecMo)
-- First Day of Receiver Month
select @.LastOfRecMo = DATEADD(d, -1, @.FirstOfNextMo)
select myVar = @.LastOfRecMo,
calc = DATEADD(d, -1, @.FirstOfNextMo)
-- Last Day of Month after Receiver Month
select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
select myVar = @.LastOfNextMo,
calc = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
----
I entered the Receiver Date in the format you recommended, although in the
actual application, the date is pulled from the database.
The first calculation correctly returns '3/1/2005' in both the myVar and
calc columns, indicating that '3/1/2005' was successfully assigned to the
variable @.FirstOfRecMo.
The second calculation, however, fails to assign the results of the DATEADD
function to @.FirstOfNextMo. It returns NULL in the myVar column, although
the calc column correctly returns '4/1/2005'.
The other two calculations return NULL in both columns.
Is the problem with my SQL Server, perhaps? Does the code run correctly on
your computer?
Your help is very much appreciated.
"David Gugick" wrote:
> Sheldon Penner wrote:
> First thing is the date format you are using is not portable. The only
> portable formats are:
> yyyymmdd
> yyyy-mm-ddThh:mm:ss.mmm(no spaces)
> But your code works fine for me with the bad date format. Try changing
> the date format and see what you get.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||This is very creepy!
When I type the code into Query Analyzer, it produces the errors I described
in my previous messages. However, if I copy the code to this newsgroup, pos
t
it, then copy the code from the post and paste it into Query Analyzer, it
runs properly.
Evidently, SQL Server is messing with my code. Any thoughts?
"Sheldon Penner" wrote:
> I thought the problem was solved, but it's not. DATEADD behaves erratical
ly
> when given a variable as an argument. The code I sent you yesterday
> inexplicably runs correctly now, but the following example, closer to what
I
> am actually using, does not:
> ----
> declare @.ReceiverDate datetime
> declare @.FirstOfRecMo datetime
> declare @.LastOfRecMo datetime
> declare @.FirstOfNextMo datetime
> declare @.LastOfNextMo datetime
> -- Receiver Date is 3/27/2005
> set @.ReceiverDate = '20050327'
> -- First Day of Receiver Month
> select @.FirstOfRecMo = cast(cast(Year(@.ReceiverDate) as char(4)) + right('
0'
> + cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
> select myVar = @.FirstOfRecMo,
> calc = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0' +
> cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
> -- First Day of Month after Receiver Month
> select @.FirstOfNextMo = DATEADD(m, 1, @.FirstOfRecMo)
> select myVar = @.FirstOfNextMo,
> calc = DATEADD(m, 1, @.FirstOfRecMo)
> -- First Day of Receiver Month
> select @.LastOfRecMo = DATEADD(d, -1, @.FirstOfNextMo)
> select myVar = @.LastOfRecMo,
> calc = DATEADD(d, -1, @.FirstOfNextMo)
> -- Last Day of Month after Receiver Month
> select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
> select myVar = @.LastOfNextMo,
> calc = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
> ----
> I entered the Receiver Date in the format you recommended, although in the
> actual application, the date is pulled from the database.
> The first calculation correctly returns '3/1/2005' in both the myVar and
> calc columns, indicating that '3/1/2005' was successfully assigned to the
> variable @.FirstOfRecMo.
> The second calculation, however, fails to assign the results of the DATEAD
D
> function to @.FirstOfNextMo. It returns NULL in the myVar column, although
> the calc column correctly returns '4/1/2005'.
> The other two calculations return NULL in both columns.
> Is the problem with my SQL Server, perhaps? Does the code run correctly o
n
> your computer?
> Your help is very much appreciated.
> "David Gugick" wrote:
>
Or at least it has always operated this way