Working with datetime variables

[tynar]

Hello,

In datasets I would like to assign a date start of year to variable.

I wrote following SQL:

DECLARE @startOfYear datetime

select @startOfYear = cast(cast(YEAR(@EndDatePar) AS VARCHAR(4)) + '0101' AS datetime) --DATEADD(YEAR ,DATEDIFF(YEAR ,0 ,@EndDatePar) ,0)

but I receive error:
"Conversion failed when converting date and/or time from character string." on clicking Receive Columns.

@EndDatePar is a parameter associated with a variable which user enters and I'm working in Designer.

Would much appreciate your help.

[HighAley]

Hello.

In datasets I would like to assign a date start of year to variable.

I wrote following SQL:

DECLARE @startOfYear datetime

select @startOfYear = cast(cast(YEAR(@EndDatePar) AS VARCHAR(4)) + '0101' AS datetime) --DATEADD(YEAR ,DATEDIFF(YEAR ,0 ,@EndDatePar) ,0)

but I receive error:
"Conversion failed when converting date and/or time from character string." on clicking Receive Columns.

@EndDatePar is a parameter associated with a variable which user enters and I'm working in Designer.

Would much appreciate your help.

Please, try to use next expression:

Code: Select all

select @startOfYear = cast((cast(YEAR(@EndDatePar) AS VARCHAR(4)) + '0101') AS datetime) --DATEADD(YEAR ,DATEDIFF(YEAR ,0 ,@EndDatePar) ,0)

Otherwise, please send us your report template with data for analysis.

Thank you.

[tynar]

I tried your suggestion without success.

Please find in attachments example report file.

[HighAley]

Hello.

Please, try to use next query:

Code: Select all

declare @startOfYear datetime

select cast(cast(YEAR(@nowDatePar) AS VARCHAR(4)) + '0101' AS datetime) col1

Thank you.