Converting Integer to Date and pass to server 1

[Flopper]

Morning,

I'm attempting to convert an integer (yyyymmdd) to a date then pass it to the server by comparing it to the current date. I'm not having much success.

I've attempted to resolve this using SQL Expressions but i had problems using the substring expression.

By using the Right & Left functions i can extract the Year and Day but not the Month.

I've searched through the forum but without much luck. I'm using XIR2.

Cheers

 

[Madawc]

Using Cdate({your.intiger}) normally works if the data is date-like. Try doing this and displaying the value, that way you can see what it makes of it and confirm that it has converted OK. Or see what's wrong, if not.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Flopper]

Thanks Madawc,

But unfortunately when i attempt this i retrieve an error from the database stating that dates must be between year 1 and year 9999. This is not a data integrity problem as i have attempted this on a small set of 10 rows.

Cheers

 

[dgillz]

Numbertodate() is a UFL that is designed to take a number in a YYYYMMDD format and convert it to a real date. I donot believe this will pass to the SQL query however.

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

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

Try something like the following SQL expression:

cdate(left({fn CONVERT(table.`number`,sql_varchar)},4)+'-'+
{fn SUBSTRING({fn CONVERT(table.`number`,sql_varchar)},5,2)}+'-'+
right({fn CONVERT(table.`number`,sql_varchar)},2))

The punctuation and what functions work depend upon your specific datasource and connectivity. If the {fn substring()} doesn't work, try:

substr(table.`number`,sql_varchar)},5,2)

-LB

 

[Flopper]

Cheers for the advice...

Unfortunately neither have been successful.

I used lbass's formula (adding CDate within a seperate formula field), but it still won't pass it to the Where clause.

The following SQL Query is created:

SELECT 
(left({fn CONVERT("calendar"."dateserial",sql_varchar)},4)+'-'+
{fn SUBSTRING({fn CONVERT("calendar"."dateserial",sql_varchar)},5,2)}+'-'+
right({fn CONVERT("calendar"."dateserial",sql_varchar)},2))
 
FROM "DWH"."dbo"."calendar" "calendar"

So it obviously recognises the SQL Command but not when i add CDate to it.

I'm using the following SQL Server driver:
Version: 2000.86.1830.00
Date: 25/03/2005

Cheers

 

[Madawc]

Try displaying the raw data using a simple Crystal report, the sort of thing the Crystal Wizard will give you. You probably have zeros, or else it's not numeric.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Flopper]

Madawc,

I've attempted this on just one record and the data is fine. I can only assume its a driver issue?!?

 

[lbass]

Did you try using cdate within the SQL expression? It doesn't turn blue, but worked here using the Xtreme database (Access-based).

-LB

 

[Flopper]

lbass,

cdate does not work in SQL expression.

I'm seriously stumped.

 

[Flopper]

A sort of update...

I have three SQL Expressions:

%Day:

{fn convert(
{fn Right("Date."Date",2)}
,
sql_smallint)}

%Month:

{fn convert(
{fn SUBSTRING({fn CONVERT("Date."Date",sql_varchar)},5,2)}
,
sql_smallint)}

%Year:

{fn convert(
{fn Left("Date."Date",4)}
,
sql_smallint)}

They combine in the formula @Date:

CDate({%Year},{%Mid},{%Day})

However if i put in the the Selection:
{@Date}=CurrentDate
it is not passed to the server.

The SQL Query shows:

SELECT 
{fn convert({fn Right("Date."Date",2)},sql_smallint)}, 
{fn convert({fn Left("Date."Date",4)},sql_smallint)}, 
{fn convert({fn SUBSTRING({fn CONVERT("Date."Date",sql_varchar)},5,2)},
sql_smallint)}

FROM "DWH"."dbo"."Date"

Still using the same SQL Driver.

Thanks

 

[lbass]

What works depends upon your datasource and driver--not sure you've specified the datasource, and I only can test for Access or Oracle. In Oracle, you could use the following SQL expression:

to_date(
{fn Left("table"."string",4)}||
'-'||{fn SUBSTRING("table"."string",5,2)}||
'-'||{fn Right("table"."string",2)},'YYYY-MM-DD'
)

Note that it appears that your field is a string, not a number, if you can use left/right and substring functions on it directly.

-LB

 

[dwelch35]

I want to thank you all for your contributions to this thread. While the solutions presented didn't work for my situation (Crystal XI R2 connecting to a DB2 database using Client Access ODBC drivers) I was able to come up with an alternative.

For anyone else who has the same setup I do, here's the formula I used to convert a numeric date format YYYYMMDD to a date format using a SQL Expression field.

cast({fn CONCAT({fn CONCAT({fn CONCAT({fn CONCAT(left(Cast("tablename"."fieldname" as varchar(8)),4), '-')}, {fn SUBSTRING(Cast("tablename"."fieldname" as varchar(8)),5,2)})}, '-')}, right(Cast("tablename"."fieldname" as varchar(8)),2))} as date)

And, if anyone has a better way of doing this given the setup described above, please let me know!

-Dave

 

[Flopper]

Thanks for all your help,

Apologies for not making it clear but i am using a SQL 2005 DB with the following ODBC driver:

Version: 2000.86.1830.00
Date: 25/03/2005

And yes, lbass... it appeared that i had my formula typed incorrect. Not sure how that happened. Big mistake, sorry.

Back to using lbass's original solution but again the CDate within the SQL Expression does not work. Nor does it work by reference through the formula editor. I've tried using a SQL Native ODBC driver but that hasn't helped either.

 

[lbass]

The syntax for the SQL expression will be specific to your datasource, so if you are using SQL Server, try searching in one of the SQL Server forums for "convert to date", and then try this in the SQL expression.

-LB