Another date format question

Howdy all.

2 questions related to dates.
Thanks in advance,
Rey

Trying to understand the reason the following displays as it
does...when attempting to save a local variable.

declare @effDate datetime
set @effDate = convert(varchar, getdate(), 101)
select @effdate
-- displays 2006-08-15 00:00:00.000

select convert(varchar, getdate(), 101)
-- displays 08/15/2006


Also, in a stored procedure, when attempting to set a passed in
variable to current date via getdate() function, I get an incorrect
syntax near '(' err. If I set the @effectiveDate var to NULL, no
problem.

ALTER procedure reportAccrualTotalsSummary_Test
/*
Purpose: provide accrual/accessment rates for each association
Inputs: associationID of selected assns,
efective date
Returns: dataset

*/

@associationID nvarchar(255),
@effectiveDate datetime = getdate()
as

blah blah

Re: Another date format question

Rey wrote on 15 Aug 2006 19:10:39 -0700:

> Howdy all.
>
> 2 questions related to dates.
> Thanks in advance,
> Rey
>
> Trying to understand the reason the following displays as it
> does...when attempting to save a local variable.
>
> declare @effDate datetime
> set @effDate = convert(varchar, getdate(), 101)
> select @effdate
> -- displays 2006-08-15 00:00:00.000

The varchar is implicitly converted back to internal date format because
you've declared the variable @effDate as datetime.

> select convert(varchar, getdate(), 101)
> -- displays 08/15/2006
>
> Also, in a stored procedure, when attempting to set a passed in
> variable to current date via getdate() function, I get an incorrect
> syntax near '(' err. If I set the @effectiveDate var to NULL, no
> problem.
>
> ALTER procedure reportAccrualTotalsSummary_Test
> /*
> Purpose: provide accrual/accessment rates for each association
> Inputs: associationID of selected assns,
> efective date
> Returns: dataset
>
> */
>
> @associationID nvarchar(255),
> @effectiveDate datetime = getdate()
> as
>
> blah blah

Read BOL, the default value must be NULL or a constant. You cannot use a
function.

Set it to NULL, and then in your proc use

IF datetime IS NULL
datetime = getdate()


Dan

Re: Another date format question

Daniel wrote to Rey on Wed, 16 Aug 2006 09:56:18 +0100:

> Rey wrote on 15 Aug 2006 19:10:39 -0700:
>
>> Howdy all.
>>
>> 2 questions related to dates.
>> Thanks in advance,
>> Rey
>>
>> Trying to understand the reason the following displays as it
>> does...when attempting to save a local variable.
>>
>> declare @effDate datetime
>> set @effDate = convert(varchar, getdate(), 101)
>> select @effdate
>> -- displays 2006-08-15 00:00:00.000
>
> The varchar is implicitly converted back to internal date format because
> you've declared the variable @effDate as datetime.
>
>> select convert(varchar, getdate(), 101)
>> -- displays 08/15/2006
>>
>> Also, in a stored procedure, when attempting to set a passed in
>> variable to current date via getdate() function, I get an incorrect
>> syntax near '(' err. If I set the @effectiveDate var to NULL, no
>> problem.
>>
>> ALTER procedure reportAccrualTotalsSummary_Test
>> /*
>> Purpose: provide accrual/accessment rates for each association
>> Inputs: associationID of selected assns,
>> efective date
>> Returns: dataset
>>
>> */
>>
>> @associationID nvarchar(255),
>> @effectiveDate datetime = getdate()
>> as
>>
>> blah blah
>
> Read BOL, the default value must be NULL or a constant. You cannot use a
> function.
>
> Set it to NULL, and then in your proc use
>
> IF datetime IS NULL
> datetime = getdate()
>
> Dan

Gah, at the end there it should have been

IF @effectiveDate IS NULL
@effectiveDate = getdate()


Dan

Re: Another date format question

Howdy, Dan.
Thanks for the reply.

I did as suggested and then realized that yup I screwed up in setting
var to datetime instead of varchar....

Thanks again,
Rey