How to change format of date prompt value

[cogb]

Hello all:

I am trying to convert the value of a date prompt to a timestamp so I can compare it against another timestamp in my SQL.  The problem is that I can't seem to change the format of how the date prompt is being stored.  It's being returned as Oct 13, 2015, for example.  I'm trying to first substring the date, then cast this into a timestamp, however, I'm running into some trouble.  The day portion of the date may be a single digit or two depending on the day of the month.

The easiest thing would be if I could change how the date is stored...perhaps to something like '13-OCT-2015'.

My temporary solution is to use either a simple text box or dropdown lists for the user to select the month day and year.  It will be much easier to convert that into a timestamp.

I'm sure there is a better way to solve this problem, and I'm just not expert enough with RS to know what that is.  I appreciate any help you can give!

Thanks,
cogb

[Lynn]

Hello all:

I am trying to convert the value of a date prompt to a timestamp so I can compare it against another timestamp in my SQL.  The problem is that I can't seem to change the format of how the date prompt is being stored.  It's being returned as Oct 13, 2015, for example.  I'm trying to first substring the date, then cast this into a timestamp, however, I'm running into some trouble.  The day portion of the date may be a single digit or two depending on the day of the month.

The easiest thing would be if I could change how the date is stored...perhaps to something like '13-OCT-2015'.

My temporary solution is to use either a simple text box or dropdown lists for the user to select the month day and year.  It will be much easier to convert that into a timestamp.

I'm sure there is a better way to solve this problem, and I'm just not expert enough with RS to know what that is.  I appreciate any help you can give!

Thanks,
cogb

I think you could try it the other way around. Cast your database timestamp to a date, which removes the time portion, and then compare that the date prompt value.


?Your Date Prompt? = CAST ( [Database DateTime], DATE )