How can I convert a date in SQLDateTime format back to a date.
I’ve got a list of filenames which I have put in an array and sorted by modified date with array.sortwith.
Now I want to display the results using shortDate and shortTime in the users native format.
I’ve seen methods where you need to break down the string into the separate parts - year, month, day etc. but is there a simple function?
Dim d As New Date
d.SQLDateTime = "2016-09-03 08:13:32"
Taken from http://developer.xojo.com/date$SQLDateTime
Of course, thanks Julian.
I didn’t realise date had a set as well as get method for SQLDateTime.
If you are creating the SQLDateTime independently, there is a gotcha.
Look out for badly formatted SQLDateTime strings that can cause an exception when you use the setter.
You may want to defensively put a try/catch around the assignment attempt.
Latest OSX Xojo 2019R1, it seems that doing
dte.SQLDateTime = MetaDict.Value("HC_Downloaded")Where MetaDict.Value('HC_Downloaded') : 2019-04-18 14:46:45
dte.SQLDateTime : 2019-04-18 14:46:45
So far the code is ok, from the debugging side , however , while adding that to the database with recP.DateColumn("testDate") = dte it seems that the code adds the date in the format [quote] 2019-04-18 00:00:00 [/quote]
Any idea why and how to fix this ? This is weird.
Thanks.
A date column in a database is usually in SQLdatetime format. How else do you expect a database to save date and time???
Well that was the whole idea , I get the date as 2019-04-18 14:46:45 and it supposed to be saved same , but instead it gets saved as 2019-04-18 00:00:00 so I loose the time part .
Just to be sure, is DateColumn in the database a Date or a Timestamp? It’s possible the engine is truncating the time, not Xojo.
It is set as DateTime in the database and I have other code with the same thing in the code side writing the correct data, for the same column. I’ll try to trim the data I take from the Dict value maybe there is a space or something that could affect the parsing but still in the debug lines I get the correct date & time while in the database it gets 0-ed
What DB is this? Here’s an example of how you could could end up with that in MSSQL due to the difference between date and datetime fields.
[code]create table #tst(d1 date, d2 datetime)
insert #tst(d1, d2)
select getdate(), getdate()
declare @d1 as datetime
declare @d2 as datetime
set @d1 = (select d1 from #tst)
set @d2 = (select d2 from #tst)
select @d1, @d2[/code]
returns
2019-05-08 00:00:00.000 2019-05-08 12:36:17.947
[quote=435548:@Scott Griffitts]What DB is this? Here’s an example of how you could could end up with that in MSSQL due to the difference between date and datetime fields.
[code]create table #tst(d1 date, d2 datetime)
insert #tst(d1, d2)
select getdate(), getdate()
declare @d1 as datetime
declare @d2 as datetime
set @d1 = (select d1 from #tst)
set @d2 = (select d2 from #tst)
select @d1, @d2[/code]
returns
2019-05-08 00:00:00.000 2019-05-08 12:36:17.947[/quote]
MySQL (MariaDB ) on Linux
So, after replacing Date with Xojo.Core.Date and using FromText and ToText issue was solved, so it seems that the issue is on Date, somehow, for the moment I will use it’s replacement and hope that it will not be discontinued soon or at least will be fixed.
So far this is what I got and how I fix it . the issue might rely on the DatabaseRecord as well maybe , no idea.
Ah, thats where the confusion comes from. You said originally
saves in the format is not the same as saves as (which is what you meant).
Glad you got it sorted though.
Aurelian, you mentioned this code to add to the database:
recP.DateColumn("testDate") = dteand you fixed the problem using Xojo.Core.Date FromText and ToText
Maybe you needed to change the code above to:
recP.DateColumn("testDate") = dte.SQLDateTimeWell that part was fixed but what worries me is that I have that like in 90 % of the app so that is a huge mess now, I’ll have to test all the app functionality and make sure I don’t loose or mess data, if that’s the case then I’ll have to adapt everywhere.
[quote=435567:@Alberto De Poo]Aurelian, you mentioned this code to add to the database:
recP.DateColumn("testDate") = dteand you fixed the problem using Xojo.Core.Date FromText and ToText
Maybe you needed to change the code above to:
recP.DateColumn("testDate") = dte.SQLDateTimetest it already , same issue. only Xojo.Core.Date fix it , no idea how. but is done.
Maybe, I used to use those a lot all over my code , now I’m not so sure I will still use them.
Did a test with SQLiteExample, added a column, used DateColumn to put a date there and the file has the correct date-time. Don’t have MySQL/MariaDB access at this moment to test more.
