Converting to Date

[junction123]

Hi

I am using SQL and Ce XI. I have a formula for the report header and I would like the date to show up as YYYMMDD rather than a string.

Currently I get the date as 2007,10,12.00. I want it as 20071012. I looked up the Date functions but cannot figure out what to use.



"International Portfolio Equity Trading Activity " + " from " + Date({?@Date}) + " to " + Date({?@Date})

Please help.

Thanks in advance
junction123

 

[jambu]

junction123 try this

totext({?@Date},"yyyyMMdd")

 

[junction123]

Jambu,

My date field is defined as an integer in the stored procedure.

I get this error:

bad number format string

thanks
junction123

 

[jambu]

try it with the Date function.

totext(Date({?@Date}),"yyyyMMdd")

 

[bdreed35]

You don't really need to covert it to a date in this situation since you are just displaying it.
With that being the case, try this:

"International Portfolio Equity Trading Activity " + " from " + totext({?@Date},0,"") + " to " + totext({?@Date},0,"")

This purely removes the decimals and thousands seperators when converting it to a string.


~Brian

 

[junction123]

I am having a similar issue and when I use the above I still get the same error.

The field in the table is stored as an integer.

I am unable to convert to a datetime field in order to be able to get the format as Mar 01 1999

totext({GetPKG_QCRSAddsRemovesGOAccounts;1.SettlementDate},0,"")

gives me 20071203. But I need to be able to format this to Mar 01 1999.

Thanks
junction123

 

[junction123]

Please help!!!

How can I convert the integer type field defined in the database table as an INTEGER like 20071203 to a datetime field?

I want it to be Mar 01 1999. I am getting errors while using various formulae.

 

[junction123]

I converted to a string. Now how do I convert the string to a datetime field?

totext({GetPKG_QCRSAddsRemovesGOAccounts;1.SettlementDate})

 

[dgillz]

Numbertodate(YYYYMMDD} converts an 8 digit integer to a real date. So replace this with your field and you are done.

You will have to download numbertodate() from the BO website.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[CoSpringsGuy]

try this:


date(cstr({GetPKG_QCRSAddsRemovesGOAccounts;1.SettlementDate},"####,##,##"))

from there I would format it to the Mar 01 1999 format by right clicking the new field selecting format field> select the date tab. From there you can format the date however you like.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[junction123]

CoSpringsGuy

u are the BEST!!!!How long did it take you to figure this out.

This worked and I needed this for today

Thanks

 

[CoSpringsGuy]

You are welcome ...

I had done something similar once so it didnt take me to long to figure it out.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection