Input Parameter Field for a Stored Procedure

[LeonelSanchezJr]

Is it possible to create an input parameter field for a stored procedure as a DATE field instead of a DATETIME.

The reason I'm asking is because when I run my Crystal Report, the prompts require the user to enter the DATE and TIME.

The user doesn't want to have to enter the time.

In the table being accessed the field is defined as DATETIME, but the length is 8. So, the field only contains a date.

 

[fluteplr]

There is no such thing as a date field in sql server, a length of 8 is bytes not characters. SQL server date times are stored in an internal format, not characters. That all being said you should be able to find a setting in Crystal to change the prompt the user sees to be only a date. If you let us know what version of Crystal you are using we could probably supply more help or you might try the Crystal forum.

 

[tlbroadbent]

When you say the user is required to enter date and time, do you refer to two separate prompts or one prompt that requires the user to input both date and time in one string? Which program is prompting the user? How does the underlying query handle the datetime column?

FYI FROM SQL BOL: "Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight."

More FYI from me: There are not separate date and time data types. If you insert or update a datetime column with only the date, the 4 bytes containing the time will be zero but will still be part of the datetime column. Zero in time represents 12:00AM or 0 milliseconds past midnight. Likewise if you insert a time, the date portion will be zero but zero references Jan 1, 1900.

It is up to the developer to understand datetime data in order to format the date and handle it properly. You can use the CONVERT function to format the date (and the time) into a variety of styles. For example; CONVERT(char(10), DateCol, 103) returns date in the style "dd/mm/yyyy." CONVERT can be used in SELECT lists and in WHERE criteria.

Example: Select records from a table which has a datetime column. Only show the date and select records that match a given date.

Select
col1, col2,
convert(char(10),datecol,101) As DateOnly,
col4
From tbl
Where convert(char(10),datecol,101)='12/16/2000' Terry

http://members.home.net/tlbroadbent/prog.htm

"I'm not dumb. I just have a command of throughly useless information." - Calvin, of Calvin and Hobbes

 

[LeonelSanchezJr]

I am using Crystal 8.5 and there is one prompt which prompt the user to select the date (through a "date picker" utility) and the time field follows the date in the same prompt.

The time part of it is not necessary since the SQL table has all zeroes in the time portion of the datetime field.