Page 1 of 1

Storing date to a database, retrieving it and displaying it

Posted: Sat Feb 27, 2016 7:39 pm
by greg falda
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

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

Posted: Sat Feb 27, 2016 7:55 pm
by Klaus
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

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

Posted: Sat Feb 27, 2016 7:57 pm
by FourthWorld
See the dateItems option for the convert command.

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

Posted: Sun Feb 28, 2016 6:53 pm
by zaxos
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.

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

Posted: Sun Feb 28, 2016 8:22 pm
by phaworth
Wondering why you are not using the standard sql date format of YYYY-MM-DD?

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

Posted: Wed Mar 02, 2016 8:00 pm
by greg falda
Thanks to all for your help.

You helped me to solve my problem :D

Best
greg falda