can I convert a number to a date?

[PushCode]

I'm pulling a number formatted field (<%# Eval("AP_PAYMENT_DATE_FK_KEY") %>) from an Oracle table, and I want to display it as a date.

Here's what it looks like in the database: 20081201

I was hoping I could do something like this # Eval("AP_PAYMENT_DATE_FK_KEY", "{0:mm/dd/yyyy}") %> to display it in a date format...but no go.

Any help would be greatly appreciated. Thanks!

 

[taree]

why do not you take care of it in your sql statement like this:

01300901

substr(l.letting,3,2)||'-'|| substr(l.letting,5,2)||'-'|| substr(l.letting,1,2) LetDate

and this is how it looks like

01-30-09

 

[PushCode]

I'm not familiar with this LetDate functionality and I don't see any documentation on it. Can you tell me what it should look like in my query? Remember, I'm querying and Oracle database.

So say here's my query:
SELECT a.AP_PAYMENT_DATE_FK_KEY, b.AC_CHECK_NUMBER FROM XXGDW_AP_PAYMENT_SCHED_F a, XXGDW_AP_PAYMENT_SCHED_MISC_D b
WHERE a.ap_inv_payment_pk = b.ap_inv_payment_pk

What should it look like using your solution? Thanks!

 

[taree]

substr(AP_PAYMENT_DATE_FK_KEY,7,2)||'-'||
substr(AP_PAYMENT_DATE_FK_KEY,5,2)||'-'||
substr(AP_PAYMENT_DATE_FK_KEY,1,4)||'-'||

using your data: 20081201 if you include the above code in your sql you should get this 01-12-2008

 

[taree]

sorry remove the last ||'-'||. it is extra

 

[PushCode]

That did it. Thank you!

 

[taree]

Glad to help.

 

[jbenson001]

Any formatting should be done in the front end, not SQL. SQL is for data retreival and manipulation, not presentaion.

 

[taree]

jbenson001, can you provide an example how this can be done differently. thanks

 

[jbenson001]

YOu can use the .NET format() method, also the .ToString("Format") with a format mask. There are many examples online and in help. I will try to post a good example late r if I get the time.

 

[jbenson001]

this should get you started:

http://msdn.microsoft.com/en-us/library/8kb3ddd4(vs.80).aspx

 

[PushCode]

Went down that road and got lost. That's why I went the SQL route instead.

 

[jbenson001]

There much more you can do with(and faster) a front end than in SQL, formatting, calculations, etc.

 

[ca8msm]

Cast it to a date in your SQL statement. Then format in your front end as Jim suggested.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers