• Registered: 2017-11-20
  • Posts: 103

Topic: Date of bird (Calculate field)

Hello, I want to put in a calculated field the date of birth of the people from the ID, this is what I put, the query is supposedly fine but inside MDV it gives me an error.  There is something I'm doing wrong and I don't know what it is. I need help with this my people, TKS

select format (convert(datetime, Left(dni,6),11),'yyyy/MM/dd') from dat_person

2 Reply by derek (edited by derek 2021-02-08 23:28:15)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Date of bird (Calculate field)

Hi,
If it's to display the date in a different format as a calculated field, then have a look at the calculated field formula in the attached (note that your calculated field needs to be defined as 'text').
If it's to display the date in a different format as an sql call, then have a look at the script in the attached for one way to do it.
Derek.

Post's attachments

Attachment icon wencalcdate.zip 337.13 kb, 275 downloads since 2021-02-09 

  • Registered: 2017-11-20
  • Posts: 103

Re: Date of bird (Calculate field)

Thanks your brother, you are amazing

  • Registered: 2017-11-20
  • Posts: 103

Re: Date of bird (Calculate field)

And if I want to obtain in a grid the list of people who have their birthday this month, how do I do it?

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Date of bird (Calculate field)

Hi,
Quite a few different ways to do it.
Rather than just limit it to birthdays 'this month', I think I'd use a filter to allow you to enter any month.
To do this, I'd use a calculated field and this could either be to filter on the 2 digit month number (01,02 etc) or filter on the 3 digit month name (Jan, Feb, Mar etc);  both ways are in the attachment.  Obviously, if english isn't the native language, you'd want to amend the calculated field accordingly.
Derek.

Post's attachments

Attachment icon wencalcdate3.zip 339.07 kb, 315 downloads since 2021-02-09 

  • Registered: 2017-11-20
  • Posts: 103

Re: Date of bird (Calculate field)

Hello brother, a question out of the situation.  How can I use calculated fields in the "SQL Query" action of the buttons?  for example in a selection of data.  I get the error that the field has not been selected.

7 Reply by derek (edited by derek 2021-02-09 23:42:47)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Date of bird (Calculate field)

Hi,
You can't. 
Calculated fields are not saved and held in actual tables - they are just the temporary results of calculations.
An example would be an item's profit - you would hold a sales price and a cost price in a table but it's redundant to hold the profit (sales price - cost price) because it can be calculated when the program runs - and when the program closes, the profit calculation no longer exists.
In your example, when the program runs, you have calculated fields that 1) reformat the dni date and 2) use the month portion of the dni date so you can use it as a filter.  They are both simply calculations based on the dni date.
If you use SQL Query therefore, you cannot refer to calculated fields.  What you must do is replicate the calculation in your SQL Query - using the above example, you would write something like
SELECT (sales_price - cost_price) as profit from my_table
Derek.

  • Registered: 2017-11-20
  • Posts: 103

Re: Date of bird (Calculate field)

Master, any idea why MVD 'now' returns a date and my computer gives me another?