Storing date to a database, retrieving it and displaying it

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
greg falda
Posts: 77
Joined: Thu Jan 15, 2015 6:51 pm
Location: Warsaw

Storing date to a database, retrieving it and displaying it

Post by greg falda » Sat Feb 27, 2016 7:39 pm

Hello

I want to store dates in an SQL database. A way to do it seems to be (for today's date):

Code: Select all

put the date into tDate
convert tDate to seconds
put it into tDateAsSeconds
and then to store the integer tDateAsSeconds in the database. My questions are:
1) When I add the line

Code: Select all

answer tDateAsSeconds
then the output is empty (and not the integer coding the date). Why?
2) Please provide code to convert the tDateAsSeconds (for instance: after retrieving it back from the database) back to a date and to display it in a readable format via

Code: Select all

answer ...
3) Is there a way to store the date in a more consistent way - i.e. as days, not as seconds
4) If you know any other ways to store the date in a database, please share them :D

Thanks in advance
greg falda

Klaus
Posts: 11057
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Storing date to a database, retrieving it and displaying

Post by Klaus » Sat Feb 27, 2016 7:55 pm

Hi Greg,

do this:

Code: Select all

...
put the date into tDate
convert tDate to seconds
## put it into tDateAsSeconds
## IT may be empty at this time, but tDate now holds the converted date!
put tDate into tDateAsSeconds
...
:D

Best

Klaus

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6716
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Storing date to a database, retrieving it and displaying

Post by FourthWorld » Sat Feb 27, 2016 7:57 pm

See the dateItems option for the convert command.
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

zaxos
Posts: 222
Joined: Thu May 23, 2013 11:15 pm
Location: Greece

Re: Storing date to a database, retrieving it and displaying

Post by zaxos » Sun Feb 28, 2016 6:53 pm

You could use CONVERT() function in you SELECT query when you get the data from the database
ex. "SELECT CONVERT(VARCHAR(24),theDate,113) FROM myTable" Where theDate is the column name.
Assuming your date is stored in seconds in your database this would return the date in dd mon yyyy hh:mi:ss:mmm.
Knowledge is meant to be shared.

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Storing date to a database, retrieving it and displaying

Post by phaworth » Sun Feb 28, 2016 8:22 pm

Wondering why you are not using the standard sql date format of YYYY-MM-DD?

greg falda
Posts: 77
Joined: Thu Jan 15, 2015 6:51 pm
Location: Warsaw

Re: Storing date to a database, retrieving it and displaying

Post by greg falda » Wed Mar 02, 2016 8:00 pm

Thanks to all for your help.

You helped me to solve my problem :D

Best
greg falda

Post Reply

Return to “Getting Started with LiveCode - Experienced Developers”