Date Function

[lawre016]

I have a field in a SQL table that displays
05-Sep-2002 16:00:31 (it's a char field). I need to convert it into the following format yyymmdd so that I can use a date range parameter in my report. (I do not need the time)

Can anyone help?

Thanks!

 

[synapsevampire]

First, SQL Server does not have a date type without a time, so we'll tweak it slightly:

Fortunately the format lends itself well to a quick conversion.

Create a SQL Expression containing:

convert(datetime, substring({table.datetimestring},1,11))

Now you can reference this field (I'll call it
{%CastedDate} in the record selection formula, as in:

{%CastedDate} >= {?MyStartDate}

Or however you intend to use the date parameters.

-k

 

[dgillz]

Why not convert your YYYYMMDD field into a real date instead?

check out the numbertodate() UFL on the crystal decisions website to do this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lawre016]

Thanks, I created a SQL expression %Date using
convert(datetime, substring(PR_DC_ND_TRG_LST.PR_SIGNOFF_DT_TRNEE,1,11)) which returns a correct Date format 05/01/2003.

Now what I need to do is be able to create a parameter in the report to choose specific date ranges such as 05/01/2003 thru 05/15/2003.

I can't figure out how to create a parameter that references this new 'field' %date.

BTW, I have Crystal 8.5 Developer.

Again, Thanks!

 

[Reebo99]

Create a parameter {?DateRange}, make it a date type and ensure range value is checked.

The goto your record selection formula and add :

{%CastedDate} in {?DateRange}

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

Try:

Insert->Field Object->Right click Parameters-New

Make it a date or datetime as required.

Now use Report->Edit Selection Formula->Record:

%date = {?MtDateParm}

Be sure to check the Database-Show SQL Query to make sure that it's getting passed to the database.

dgillz: That's what the SQL expression does, creates a datetime field

-k

 

[lawre016]

Perfect - Report is done.

Thanks to all!