Date Format

[sullyman]

What is the best way to handle date fields in an SQL2005 table when working through Filemaker.

In SQL, there is two options for date but both include a timestamp.

How can users just enter a date etc. in Filemaker.

Thanks,

Sully

[LaRetta]

Hi Sully,

Are you talking about pulling SQL data into FM through ODBC import, SQL query script statement? Via calculation or SQL statement? Or exporting? FileMaker doesn't care. Can you explain more specifically what you need?

LaRetta

[sullyman]

Hi LaRetta,

Yes, i am pulling data from SQL into FM so users can work on live SQL data.

I would like users to enter dates etc. in FM Layouts etc. and the data writes back to SQL and wondered if there are any problems with entering dates through FM to a live SQL table etc.

Was just wondering also if there was any good way to show SQL Date data without timestamps etc. Is making the field smaller the only way to hide timestamp etc.?

Thanks,

Sully

[LaRetta]

Hi Sully,

I still don't quite see what you are doing. If you are displaying SQL data in FM and changing the SQL data directly then I can't advise - I've never done it. If you are importing SQL then exporting and the SQL fields are timestamp and must STAY timestamp, then you will need to 1) have your Users enter timestamps (into the timestamp field) or 2) use a date field (which your Users will use) and then have that SQL timestamp field have an Auto-Enter (Replace) calculation equaling that date field.

When FM (in the timestamp field) gets that date input but not the time, it automatically adds midnight for you. In fact, many SQL dates display 12:00:00.00 and they don't use times either! But SQL Server DOES recognize dates as 'YYYY-MM-DD'.

I hope I've given you enough to work on; otherwise, I'll need a bit more information to understand specifically how you are using those date/timestamp fields. And if you are writing back directly to the SQL tables then hopefully others will jump in to assist us.

LaRetta :wink2:

[sullyman]

Hi LaRetta,

Yes, i want users to change SQL data directly through FM.

I can overcome this by changing the Field Format of the SQL Field to a Drop-Down Calendar which users can then select a date and the timestamp is added automatically so SQL is happy.

The problem i am having is that i would like to do a calculation on the first date entered so the next five fields will automatically fill in with dates calculated by the calculation.

I've tried an Auto-Enter calculation on Field 2 where Field 2 = Field 1 + 3.

This works fine on ordinary FM Date fields but not on SQL Timestamp fields. Has anyone got this to work?

Thanks

[sullyman]

I have got the auto calculation for dates working on SQL timestamp fields through FM but when i go to print report etc, the Date Mergefield shows the correct date but also timestamp also.

Any way i can get rid of this timestamp entry from reports?

IF i configure a Text field in SQL and enter dates in FM directly to SQL, is there someway that FM will know these are dates?

[LaRetta]

Any way i can get rid of this timestamp entry from reports?

Not that I know of, Sully, I've wanted it also.

IF i configure a Text field in SQL and enter dates in FM directly to SQL, is there someway that FM will know these are dates?

Oh, please don't switch to text for dates. If you have a timestamp in FM, you can make it a date by creating a calculation (result is date) = Timestamp field

I just don't understand what you are trying to do that you aren't getting. Maybe it would help if you provided us the purpose and context. Otherwise, I'm just answering (or trying to answer) bits and pieces of questions with one eye blindfolded.

LaRetta (the pirate)

.... okay, but we don't have a pirate emoticon...

[sullyman]

Hi LaRetta,

Thanks for reply.

I have a SQL Table which i want users to input directly through FM.

Some of these fields are date fields which i require for a print report configured in FM.

The field in SQL is configured as a datetime field so appears as a timestamp field in FM. I can enter dates fine whilst hiding the timestamp entry but when i go to print reports in FM, the timestamp entry is also appearing.

On my FM Print report, i have inputted a MergeField for this field and so i'm getting the correct date but unneccessary timestamp entry.

Hope this explains a bit more.

Can you tell me more about what you replied and how i can achieve this - i.e. If you have a timestamp in FM, you can make it a date by creating a calculation (result is date) = Timestamp field

Thanks,

Sully

Oh, please don't switch to text for dates. If you have a timestamp in FM, you can make it a date by creating a calculation (result is date) = Timestamp field

I just don't understand what you are trying to do that you aren't getting. Maybe it would help if you provided us the purpose and context. Otherwise, I'm just answering (or trying to answer) bits and pieces of questions with one eye blindfolded.

LaRetta (the pirate)

[sullyman]

Hi LaRetta,

Think i got it.

I created another Calculation FM Field and set it to equal the Timestamp filed.

Then i placed the new Calculation Field as a Merge Field on my report and Bingo :

Thanks a mill

Sully

[sullyman]

Hi LaRetta,

Just another quick question please.

As i said i have created a timestamp field in SQL which i am inputting directly through FM. As you explained to me, the calc function works fine.

What i want to know is what if i had the format of the field as Text(Varchar) etc. in SQL to start with as i don't really need it to be a timestamp field in SQL.

In only need it to be datefield in FM for reporting etc. So if i changed the Field in SQL to a varchar field and then run the Date Calc again on the text field from SQL. Would you recommend this or can you see problems down the line?

Thanks,

Sully

Hi LaRetta,

Think i got it.

I created another Calculation FM Field and set it to equal the Timestamp filed.

Then i placed the new Calculation Field as a Merge Field on my report and Bingo

Thanks a mill

Sully

[sullyman]

Have another calucaltion to solve.

Users fill in one date and then there is a calculation that fills out the rest of the fields. What i have done to achieve this is create a calculation that adds the correct number of seconds etc. to each field to form the correct date when an entry is placed in the previous field. This is working fine.

I now have one field where users enter the number 10 for 10 days. Sometimes though this could be 5 for 5 days or 3 for 3 days etc. etc.

How can i run a calc so that if a user puts in 10 (or 3 or 5 etc.), the next field will update to 10 (or 3 or 5 etc.), days in the future etc.

Any help appreciated

[sullyman]

Managed to get this one

I did a Calc of (Field 1) + (Field2 * 86400)