Creating Date from 3 separate data items

[Developer1]

Hello,
I have created 3 data items- one for Day, another for Month and a third for year.  They are all integer variables.  Their names are [Day], [Month] and [Year].  I need to now combine them into a working date (I don't need time).

I have tried _make_timestamp ([Month],[Day],[Year]) but this is returning an error: UDA-SQL-0144 An arithmetic exception was detected.[FreeTDS][SQL Server]Conversion failed when converting date and/or time from character string.

Any ideas?

Thank you.

[bus_pass_man]

Are you really sure that the query items have a data type of Int 'cause UDA thinks that at least one of them -- or its ancestor -- isn't an int.

_make_timestamp produces a time stamp, which will be the date and the time such as 2019-05-04 17:43:00 etc., which you say you don't want, so even if you could get over the data type issue you'll end up with something you don't want, although you could then cast it to a date.

You might want to use cast in an entirely different approach, avoiding _make_timestamp altogether:  cast allows you to cast a string to a date.   IIRC the syntax is something like cast ( {your expression, which you want to produce something like 20190504}, date )

Why are you trying to create such an elementary thing in report studio?   Why isn't it already in your model?  If its there you and everyone else can use it in any report. For that matter, why isn't it already in your db?  If its there then you won't have to take a performance hit producing your date column.

[MFGF]

Hello,
I have created 3 data items- one for Day, another for Month and a third for year.  They are all integer variables.  Their names are [Day], [Month] and [Year].  I need to now combine them into a working date (I don't need time).

I have tried _make_timestamp ([Month],[Day],[Year]) but this is returning an error: UDA-SQL-0144 An arithmetic exception was detected.[FreeTDS][SQL Server]Conversion failed when converting date and/or time from character string.

Any ideas?

Thank you.

Hi,

I completely agree with everything bus_pass_man posted above, but also wanted to point out that your arguments are in the wrong order for _make_timestamp() to work. The arguments should be like this:

_make_timestamp ( [Year], [Month], [Day] )

You could then cast the results of this to a date.

Cheers!

MF.

[Developer1]

Thank you for the replies.  Really helpful!

The original data item from which I created [Day], [Month] and [Year] is a character variable in which multiple types of data are held.  It is a data item that we made after the original data model was created, and we enter text items as well as date items.  To select the date entries requires a filter and then I must convert these date entries into actual, usable date items.  Thus the question to the forum.

bus_pass_man:
cast ( {your expression, which you want to produce something like 20190504}, date ) did not work, but cast(cast ([data item] as timestamp),date) did work and returns 'Feb 4, 2016'.  Thank you for the input!

Developer1:
Your suggestion fixed the timestamp issue.  Many thanks.

[MFGF]

Developer1:
Your suggestion fixed the timestamp issue.  Many thanks.

Developer1? Isn't that you?? :)

MF.

[Developer1]

Oops.  Yeppers.  Sorry about that.  Thank you for your help MFGF.