SQL Date

Creating desktop or client-server database solutions?

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

Post Reply
ace16vitamine
Posts: 34
Joined: Fri Apr 13, 2018 1:53 pm

SQL Date

Post by ace16vitamine » Wed Jun 20, 2018 9:15 pm

Hi!

In my MS SQL Database i have a date field:
2016-01-06 14:20:52.000

Now I am searching for two thinks:

1) In Livecode, I have a drop down list:
Last 7 Days
Last 30 Days
Last 180 Days

My Idea is to create a select like "SELECT dbdate from TABLE where dbdate > dropdown". Any Ideas for the Select?


2) In the other case that the select is "SELECT dbdate from TABLE" I receive a lot of Dates:

2016-01-06 14:20:52.000
2016-01-04 14:30:52.000
2016-01-01 13:20:52.000
(..)

How can I convert this to a readable format in livecode like 06.01.2016

to work with it:

if SQLselect > the date then
(..)


Thanks
Stefan

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

Re: SQL Date

Post by Klaus » Wed Jun 20, 2018 9:31 pm

Hi Stefan,

1. Sorry have to read up the SQL docs for the correct syntax for this...

2. You need to do this manually like:

Code: Select all

...
put "2016-01-06 14:20:52.000" into tDaten
put word 1 of tDaten into tDatum
set itemdel to "-"
put item 2 of tDatum & "." & item 3 of tDatum & "." item 1 of tDatum into tDeutschesDatum
...
Best

Klaus

ace16vitamine
Posts: 34
Joined: Fri Apr 13, 2018 1:53 pm

Re: SQL Date

Post by ace16vitamine » Wed Jun 20, 2018 11:39 pm

Klaus wrote:
Wed Jun 20, 2018 9:31 pm

2. You need to do this manually like:

Code: Select all

...
put "2016-01-06 14:20:52.000" into tDaten
put word 1 of tDaten into tDatum
set itemdel to "-"
put item 2 of tDatum & "." & item 3 of tDatum & "." item 1 of tDatum into tDeutschesDatum
...
Perfect Klaus, you are the best :-)

Thank you
Stefan

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

Re: SQL Date

Post by Klaus » Thu Jun 21, 2018 11:52 am

Hi Stefan,

1. You need to calculate the date from your dropdown and use my script below to convert this to SQL date fpr your SQL command.
How do you access the value of your dropdown?
"the label of btn xxx"?

Whatever, here a little function that will convert TODAYs date to any days back in the past.
You will only have to pass the number of days in the past to the function:

Code: Select all

function date_in_past num_of_days
   put the date into tDate
   convert tDate to dateitems
   subtract num_of_days from item 3 of tDate
   convert tDate to system date
   
   ## I'll leave the conversion to SQL up to you :-)
   ## Should take place right here:
   
   return tDate
end date_in_past
Tested and works!

Best

Klaus

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 641
Joined: Fri Jun 27, 2008 9:00 pm

Re: SQL Date

Post by Mikey » Thu Jun 21, 2018 2:49 pm

SQL dates are one of many routines (not just about dates, either) included in "Master Library".
https://www.dropbox.com/s/3wpwn3hfbmpl7 ... ecode?dl=0

AxWald
Posts: 324
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQL Date

Post by AxWald » Fri Jun 22, 2018 11:26 am

Hi,

Tired of having so many dateTime conversion utilities I recently wrote a "function to bind 'em all".

The idea is to trow it a dateTime string of whatever format and have it decide itself what it is, and to convert it to dateItems at first.
DateItems because it's the most easy way to calculate with dateTimes - add/ subtract days here.
And then have it output a nicely formatted dateTime, depending on the provided format designator - or empty, if no date could be recognized.

Since there's so many date formats so differently formatted ("MM/DD/YY", "DD.MM.YYYY", "YYYY-MM-DD", to show only a few numeric-only examples), and since I'm lazy, and since I my programs are only used in the german speaking part of the world, my "format-guessing" expects these formats: "DD.MM.YYYY hh:mm" and "DD. MonthName YYYY, hh:mm" for system dateTimes. A comment containing "###" designates these cases. Else I'm heavily counting on "system date" in converting.

This function seems to be quite fast & reliable, but isn't extensively tested yet. Be warned, there may be quirks!
I'm posting it here because it shows quite some ways to handle dateTimes, and it may be helpful for someone.
Should run on any version of LC, for non-german systems adaptions will be required :)

Have fun!

Call: put formatDate("2018-6-29 13:34","SSYD",2)
Result: 01.07.18
XPlain: Give me the short system date (2digit year) of the DateTimeString (a SQL dateTime), with 2 days added

Code: Select all

function formatDate what, theTarget, dayAdd
   --  what: Str - any date string
   --  theTarget: Str - the desired format to return, look up the second switch structure
   --  dayAdd: SigInt - days to add or subtract (negative values) 
   if theTarget is empty then put "SYSD" into theTarget  --  default
   
   --  at first: cleanup:
   repeat
      if char -1 of what = " " then  --  kill trailing spaces
         delete char -1 of what
      else exit repeat
   end repeat
   replace "," with " " in what  --  kill commata
   repeat                                           --  kill double spaces
      if "  " is in what then
         replace "  " with " " in what
      else exit repeat
   end repeat
   --  then decide what we have:
   put empty into myVar
   repeat for each char C in what
      if C is a number then put "N" after myVar  --  (Numeric)
      if C = "." then put "P" after myVar  --  (Point)
      if C = "-" then put "M" after myVar  --  (Minus)
      if C = " " then put "S" after myVar  --  (Space)
      if C = ":" then put "D" after myVar  --  (Double Dot)
      if (charToNum(C) > 64 AND charToNum(C) < 91) OR (charToNum(C) > 96 AND charToNum(C) < 123) then put "C" after myVar  --  (Char)
   end repeat
   --  myVar can be analyzed now:
   switch
      case ("N" is in myVar) AND ("P" is in myVar) AND ("C" is in myVar) AND NOT ("D" is in myVar)
         put "LNGD" into myFormat  --  13. May 2018  --  ### Long Date, SYSTEM SPECIFIC
         convert what from long system date to dateitems
         break
      case ("N" is in myVar) AND ("P" is in myVar) AND ("C" is in myVar) AND ("S" is in myVar) AND ("D" is in myVar)
         put "LNGT" into myFormat  --  13. May 2018 13:45  --  ### Long DateTime, SYSTEM SPECIFIC
         convert what from long system date and system time to dateitems
         break
      case ("N" is in myVar) AND ("P" is in myVar) AND NOT ("C" is in myVar) AND NOT ("D" is in myVar)
         put "SYSD" into myFormat  --  13.7.2018  --  ### Short Date, SYSTEM SPECIFIC
         convert what from system date to dateitems
         break
      case ("N" is in myVar) AND ("P" is in myVar) AND NOT ("C" is in myVar) AND ("S" is in myVar) AND ("D" is in myVar)
         put "SYST" into myFormat  --  13.7.2018 13:45  --  ### Short DateTime, SYSTEM SPECIFIC
         convert what from system date and system time to dateitems
         break
      case ("N" is in myVar) AND ("M" is in myVar) AND NOT ("D" is in myVar)
         put "SQLD" into myFormat  --  2018-07-13  --  SQL Date
         set itemdel to "-"
         put item 3 of what & "." & item 2 of what & "." & item 1 of what into what
         convert what from system date to dateitems
         break
      case ("N" is in myVar) AND ("M" is in myVar) AND ("S" is in myVar) AND ("D" is in myVar)
         put "SQLT" into myFormat  --  2018-07-13 13:45  --  SQL DateTime
         set itemdel to space
         put item 1 of what into myVar
         set itemdel to "-"
         put item 3 of myVar & "." & item 2 of myVar & "." & item 1 of myVar into myVar
         set itemdel to space
         put myVar into item 1 of what
         convert what from system date and system time to dateitems
         break
      default
         put empty into myFormat    --  couldn't resolve
   end switch
   if myFormat is empty then return empty  --  cannot find a date that I know :/
   
   --  what is now dateItems - maybe we must add some days?
   if dayAdd is not empty then
      set itemdel to comma
      add dayadd to item 3 of what
   end if
   
   --  formatting the output:
   switch theTarget
      case "LNGD"
         convert what from dateItems to  long system date 
         break
      case "LNGT"
         convert what from dateItems to long system date and system time
         break
      case "SYSD"
         convert what from dateItems to system date
         break
      case "SYST"
         convert what from dateItems to system date and system time
         break
      case "SSYD"  --  Short (Short System Date) - 2digit year
         convert what from dateItems to system date
         set itemdel to "."
         delete char 1 to 2 of item 3 of what
         break
      case "SSYT"  --  Short (Short System DateTime) - 2digit year
         convert what from dateItems to system date and system time
         set itemdel to "."
         delete char 1 to 2 of item 3 of what
         break
      case "SQLD"
         convert what from dateItems to dateitems
         set itemdel to comma
         put item 1 of what & "-" & format("%02s", item 2 of what) & "-" & format("%02s", item 3 of what) into what
         break
      case "SQLT"
         convert what from dateItems to dateitems
         set itemdel to comma
         put item 1 of what & "-" & item 2 of what & "-" & item 3 of what & \
               " " & item 4 of what & ":" & item 5 of what into what
         break
      default
         return what  --  shouldn't happen ...
   end switch
   return what  --  wOOOt!
end formatDate
Livecode programming until the cat hits the fan ...

bogs
Posts: 2312
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQL Date

Post by bogs » Fri Jun 22, 2018 12:11 pm

Nice, also looks easily extensible. Good job!
Image

ace16vitamine
Posts: 34
Joined: Fri Apr 13, 2018 1:53 pm

Re: SQL Date

Post by ace16vitamine » Fri Jun 22, 2018 3:59 pm

OK, I extended the select with

Code: Select all


put 5 into zeitraum_stunde
put "select orders from torder where creation >= DATEADD(MINUTE,-"& zeitraum_stunde &",GETDATE()) into sql34
to get only the last 5 houres as example.


Thanks!

Stefan

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

Re: SQL Date

Post by Klaus » Fri Jun 22, 2018 4:39 pm

This (DATEADD) is a SQL-Server command, sure this works with SQLite?
See here: https://www.sqlite.org/lang_datefunc.html

AxWald
Posts: 324
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQL Date

Post by AxWald » Fri Jun 22, 2018 5:26 pm

Hi,

in SQLite, try this:
SELECT DATE('now','+1 day')
or
SELECT DATETIME('now','+2 hour')
;-)

Have fun!
Livecode programming until the cat hits the fan ...

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

Re: SQL Date

Post by Klaus » Fri Jun 22, 2018 6:06 pm

Why on earth is everyone ruining my pedagogical approach! :D

AxWald
Posts: 324
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQL Date

Post by AxWald » Sat Jun 23, 2018 8:13 am

Hi Klaus,

no intention in ruining anything, sry!
Only emphasizing the sheer beauty of the subtle differences in the various SQL dialects (for mySQL, you'd lookup ADDDATE/ DATE_ADD, btw ...) 8)

Have fun!
Livecode programming until the cat hits the fan ...

ace16vitamine
Posts: 34
Joined: Fri Apr 13, 2018 1:53 pm

Re: SQL Date

Post by ace16vitamine » Sun Jun 24, 2018 6:55 pm

Klaus wrote:
Fri Jun 22, 2018 4:39 pm
This (DATEADD) is a SQL-Server command, sure this works with SQLite?
See here: https://www.sqlite.org/lang_datefunc.html
As I wrote in the beginning:
In my MS SQL Database i have a date field:
My query is working with the lines I wrote on MS SQL Server. :-)

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

Re: SQL Date

Post by Klaus » Sun Jun 24, 2018 7:05 pm

Ooops, sorry, my fault! :oops:

Post Reply

Return to “Databases”