change midnight time format in code.

24:00 is not a valid time.  The DateTime data type contains BOTH date AND time, ALWAYS.  Time goes from 00:00 to 23:59

Internally dates are stored as double precision numbers with the integer being the number of days since 12/30/1899 (for Jet and ACE, Excel and SQL Server use a different origin date to correlate to 0.00.  Time is the fraction of an hour past midnight so
1.25 = 12/31/1899 12:15

0.25 = 12/30/1899 12:15

-1.25 = 12/29/1899 12:15

What EXACTLY is the problem you are trying to solve?

Hi,

What EXACTLY is the problem you are trying to solve?

We have code to import these data records into Caspio.
Now we realized that for those records with time 00:00 only the date portion gets imported, while the time gets truncated/ignored.
and this is what they sent to us.

You can only import date&time values in the US format as per follow:

mm/dd/yyyy hh:mm:ss AM or PM

Please make sure that you follow above format while importing data.
Regards,
Caspio Support

Thanks,
Ben

If you are using 24 hour notation, then you do not need AM or PM

--
SQL Server origin date that correlates to 0.00 is 1900-01-01
and are stored as sets of integers, not as a single double precision number.
i.e. similar to Access/Excel but not the same.

[edit]
cross-over, hadn't seen note about the US date format.

It is quite likely that Caspio simply does not show time when it is 00:00.

Hmm, Just sent them an email asking this q, who knows..-:)
Thanks,
Ben

Only the shadow knows ;)

You can only import date&time values in the US format as per follow:
mm/dd/yyyy hh:mm:ss AM or PM

Then, I guess, resistance if futile.
Obey, and apply the requested format:

UsTextDate = Format(TrueDate, "mm\/dd\/yy hh\:nn\:ss AM/PM")

Select allOpen in new window

@Gustav,
tried yours and got response from the server

{"Code":"InvalidInputValue","Message":"Cannot perform operation because the value doesn't match the data type of the following field(s): ScheduleStartTime"

@Pat,
Meanwhile caspio is investigating it...will keep you posted.
Thanks,
Ben

Caspio seems confused.

Json typically expects dates either as a Unix timestamp, or in the ISO sequence, like:

"date": "2018-09-19"

Select allOpen in new window

While waiting, you could try to feed that to your Json converter:

TextDate = Format(TrueDate, "yyyy\-mm\-dd")

Select allOpen in new window

actually when I try this 2018-08-01 12:00:00 AM it does not give me an error, however original problem remains, no time displayed just the date.
Thanks,
Ben

below is a JSON snippet from https://howto.caspio.com/web-services-api/rest-api/older-rest-api-versions/file-operations/

"Result": [{
             "ContentType": "caspio/folder",
             "Name": "Marketing Folder",
             "ExternalKey": "75143333-6f43-4dc5-be65-db4425e45a74",
             "DateCreated": "2018-10-11T10:47:23.85"
            },
            {
             "Size": 15012,
             "LastModified": "2018-10-10T14:09:02.74",
             "ContentType": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
             "Name": "Mutual NDA.docx",
             "ExternalKey": "ab7f9ce0-c888-48b2-912a-9f7e4ce5333a",
             "DateCreated": "2018-10-04T13:55:28.817"
            },
            {
             "Size": 2133,
             "LastModified": null,
             "ContentType": "image/jpeg",
             "Name": "banner.jpg",
             "ExternalKey": "59fc2fb3-5a2b-4f0d-bf70-cd1d5f3c17ad",
             "DateCreated": "2018-08-04T10:06:53.503"
            }]

Select allOpen in new window

Do you really believe that MM/DD/YYYY hh:mm AM/PM is the ONLY way data gets imported to Caspio?

            "DateCreated": "2018-10-11T10:47:23.85"

@PortletPaul,

Do you really believe that MM/DD/YYYY hh:mm AM/PM is the ONLY way data gets imported to Caspio?

Agree, and in fact all our data is being imported with format YYYY-MM-DD HH:MM without seconds and without AM/PM, and all working fine.

Therefore it makes sense what Pat mentioned

It is quite likely that Caspio simply does not show time when it is 00:00.

And this is the latest I got from them...

We have opened a ticket # 28468 and escalated the issue to our QA team and let you know as soon as we hear back from them.

Thank you for understanding.

Regards,
Caspio Support

Will keep you posted.

btw, surprised seeing you here, I'm used to deal with with you in SQL related issues along with Scott Pletcher..-)

Thanks,
Ben

Isn't

2018-08-01 12:00:00 AM

incorrect?  Noon is PM so 12:00:00 cannot be AM

Interestingly:

print cdate(#11/14/18 12:00:00 PM#)
11/14/2018 12:00:00 PM
print cdate(#11/14/18 12:00:00 AM#)
11/14/2018
print cdate(#11/14/18 11:59:59 AM#)
11/14/2018 11:59:59 AM

There was no error message for the second print statement but clearly Access doesn't like AM for this time

hi,
just tested in Caspio

08/01/18 12:00:00 AM
08/01/18 00:00:00 AM
as mentioned works.

08/01/18 24:00:00 AM
returns

{"Code":"InvalidInputValue","Message":"Cannot perform operation because the value doesn't match the data type of the following field(s): ScheduleStartTime"

Thanks,
Ben

Then I think you found a bug.  12:00:00 should only exist if you are using a 24 hour clock and so AM/PM will not show.  If you are using AM/PM, then 11:59:59 is the max value.  What is the underlying RDBMS that Caspio is using or is Caspio an RDBMS?  I am not familiar with it.

Send #11/14/2018 12:00:01# and see whether Caspio displays it as AM or PM when you switch to AM/PM view.  Caspio should think that  #11/14/2018 12:00:01 AM# is invalid.

There is no bug.
12:00:00 exists whether using 12 or 24 clock.
00:00:00 only exists for 24 hour clock
24:00:00 never exists

Even though, CDate does accept "08/01/18 00:00:00 AM", ignoring the time part.

So, Caspio should accept any date/time formatted this way:

TextDate = Format(TrueDate, "mm\/dd\/yy hh\:nn\:ss AM/PM")

Select allOpen in new window

12:00 Is PM according to Access NOT AM.  That is the distinction.  I left out the "PM" part in my statement

Send #11/14/2018 12:00:01# and see whether Caspio displays it as AM or PM when you switch to AM/PM view.

Dont see an option to display AM/PM.
See attached.

Caspio should accept any date/time formatted this way:

As mentioned it does accept, however w/o displaying the time.
see ScheduleStartTime on attached.


Thanks,
Ben
Untitled.png

Look at the properties of the field in design view.

It doesn't have this under tables, but in general app settings I see it and its set for AM/PM display, not sure why its not displaying it, see attached.

Thanks,
Ben
Untitled.png

I see you need a four-digit year.
So, in your query that you use as source when generating the csv file, specify the date/time using Format:

ScheduleStartTime: Format(StartTime, "mm\/dd\/yyyy hh\:nn\:ss AM/PM")

Select allOpen in new window

We still haven't answered the initial question which is - is time supressed when it is 00:00:00?

So, try entering that time value, leave the record and what happens when you go back.  That will tell you whether or not you even have a problem.

enter the zeros both with and without the AM designation to see if that matters.

REMEMBER - date/time is not stored as a string.  It is stored in a numeric format so that arithmetic is easy.  Dates are converted on input and output so people can see them in whatever regional format they use.  Date/time data would be the ultimate Tower of Babel if it were stored as strings according to local settings.

ScheduleStartTime: Format(StartTime, "mm\/dd\/yyyy hh\:nn\:ss AM/PM")

same problem.

So, try entering that time value, leave the record and what happens when you go back.  That will tell you whether or not you even have a problem.

enter the zeros both with and without the AM designation to see if that matters.

Tried that manually, and Immediately after exiting that record I get to see a date value only.

tried exporting to CSV and also only includes date portion.

Guess will have to wait for Caspio to finish their investigation on this...

Thanks,
Ben

We still have no idea what your

JsonConverter.ConvertToJson(patient)

Select allOpen in new window

actually does, nor what data you pass to it. If you pass it your date values as data type Date, that function is responsible for the final format exported.

As for the conversion of dates with or without a time part, Access can do this:

"Id","Date","TextDate"
1,2015/04/04 00:00:00,"04/04/2015 12:00:00 AM"
2,2011/09/09 13:17:00,"09/09/2011 01:17:00 PM"
4,2012/03/12 08:43:00,"03/12/2012 08:43:00 AM"
5,2013/04/03 00:00:00,"04/03/2013 12:00:00 AM"
19,2013/10/02 00:00:00,"10/02/2013 12:00:00 AM"
20,2013/11/03 00:00:00,"11/03/2013 12:00:00 AM"
23,2013/11/05 00:00:00,"11/05/2013 12:00:00 AM"
33,2015/01/12 00:00:00,"01/12/2015 12:00:00 AM"
34,2014/02/22 00:00:00,"02/22/2014 12:00:00 AM"
43,2015/04/09 00:00:00,"04/09/2015 12:00:00 AM"
44,2015/04/24 00:00:00,"04/24/2015 12:00:00 AM"
45,2015/04/04 00:00:00,"04/04/2015 12:00:00 AM"
46,2015/04/04 00:00:00,"04/04/2015 12:00:00 AM"
47,2015/04/04 15:23:15,"04/04/2015 03:23:15 PM"
48,2015/06/11 00:00:00,"06/11/2015 12:00:00 AM"
49,2014/06/11 00:00:00,"06/11/2014 12:00:00 AM"
50,2014/06/11 00:00:00,"06/11/2014 12:00:00 AM"
51,2015/06/11 00:00:00,"06/11/2015 12:00:00 AM"
52,2018/10/03 00:00:00,"10/03/2018 12:00:00 AM"
53,2018/01/02 00:00:00,"01/02/2018 12:00:00 AM"
54,2018/02/01 00:00:00,"02/01/2018 12:00:00 AM"

Select allOpen in new window

See the attached demo, please.
Export.accdb
Export.csv

Guess will have to wait for Caspio to finish their investigation on this...

OK, you have proven that this is a display issue.  What is the problem?  As long as the user understands that a "missing" time is "midnight", who cares?  This is how Access works unless you force the date format to always show.  Caspio can't do something that Access can do.  If Access is better than Caspio, why are you converting?

Hi Experts,
Just got back from Caspio the following.

Hi Ben,

We just heard back from Engineering team that we do not display time in case if it is midnight (00:00:00 or 12:00 AM). So it is working as per design. Please let us know if you have further questions on this inquiry.

Thanks,

So unless you have a way of over-smarting Caspio (In that case I may open a new thread..), this is for now...
Thanks to all participants!