Forcing year to 4 characters

system · October 20, 1998, 3:22pm

We are in the process of making our reports comply with our company’s Y2K guidelines. Right now, our BO reports’ prompts accept either a 2 or a 4-digit year in the date prompts. Is it possible to force the user to enter a 4-digit year? (We are using SQL Server 6.5).

A second question is can we force the date formats in a UserResponse formula to display 4-digit years if the user enters only a 2-digit year. I have tried the ToDate() function with a format string of “mm/dd/yyyy” on a date like 8/1/98, but the result is still 8/1/98.

Thanks,
Michael

Listserv Archives (BOB member since 2002-06-25)

system · October 21, 1998, 5:01pm

Michael,

There’s no easy way to force the user to enter a 4-digit year. You could do it by extracting the different part of the dates so that you force users to enter a 4-digit year for the year part, but this means a couple of things:

You would need to define separate prompts for different parts of the date,
You may need to define separate objects for each date part, but this means you lose your index on this column.

You could also do it by using @script and creating a script to get the user’s input. From within the script, you could force the user to enter only 4-digit year codes. I don’t have a sample script for this purpose, but maybe a scripting guru out there can crank one out rather quickly.

The above are just ideas, but I do have a solution for you for your second request.
To see the 4-digit year from a UserResponse function (I’m assuming you would do this if you cannot force the user to enter a 4-digit year), try the following:
=If
(SubStr(UserResponse(DataProvider(),“date”),7,2) <> “19”) Then
(SubStr(UserResponse(DataProvider(),“date”),1,6) & “19” &
SubStr(UserResponse(DataProvider() ,“date”),7,2)) Else UserResponse(DataProvider() ,“date”)

Also, the above formula requires the user to always use 2-digit numbers for months and dates smaller than 10 (e.g. 01, 02, 03, etc.) You could expand the formula so as to not enforce this rule and have it automatically account for 1-digit or 2-digit numbers, but it will get rather complicated. I will leave that exercise to you.

Regards,
Luis Gonzalez

From: Michael Malone [SMTP:mmalone@A.CRL.COM]

We are in the process of making our reports comply with our company’s Y2K guidelines. Right now, our BO reports’ prompts accept either a 2 or a 4-digit year in the date prompts. Is it possible to force the user to enter a 4-digit year? (We are using SQL Server 6.5).

A second question is can we force the date formats in a UserResponse formula to display 4-digit years if the user enters only a 2-digit year. I have tried the ToDate() function with a format string of “mm/dd/yyyy” on a date like 8/1/98, but the result is still 8/1/98.

Listserv Archives (BOB member since 2002-06-25)

system · October 27, 1998, 5:04pm

From: Michael Malone[SMTP:mmalone@A.CRL.COM]

We are in the process of making our reports comply with our company’s Y2K guidelines. Right now, our BO reports’ prompts accept either a 2 or a 4-digit year in the date prompts. Is it possible to force the user to enter a 4-digit year? (We are using SQL Server 6.5).

Well, we run Oracle, so the answer needs not be useful for you, but… If you use @Prompt function for prompt, then you can specify type for user’s response.
If you specify it as D for date - everything will work OK for you. On the other hand, if you generate prompt from objects in universe, it uses @Variable function,
which always treates user’s response as text. So, you should use ToDate function on your prompt.

Unfortunately, I don’t know of a better way to add this function than openin SQL window,
adding to_date and marking “Do not generate SQL before running” or crerating a special
prompt object with Designer… Anyone happens to know a way to have:

“Object equal to function(prompt)”

in Data Provider Editor? Just curious, I do have a Designer module fortunately…

A second question is can we force the date formats in a UserResponse formula to display 4-digit years if the user enters only a 2-digit year. I have tried the ToDate() function with a format string of “mm/dd/yyyy” on a date like 8/1/98, but the result is still 8/1/98.

To force a specific display of date, use FormatDate(Date,“FormatString”) - if your date is of type date.
If you have your date as string, simply use FormatDate(ToDate(“YourDateString”,“FormatString1”),“FormatString2”), where FormatString1 specifies how to convert user’s response to Date format and FormatString2 - how to
display your date… Easy, isn’t it?

Ryszard Mikke

–==> Hiroshima’45 Tschernobyl’86 Windows’95 <==-- R.Mikke@pl.vwfsag.de

Listserv Archives (BOB member since 2002-06-25)