SQL Date
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 130
- Joined: Fri Apr 13, 2018 1:53 pm
SQL Date
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
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
Re: SQL Date
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:
Best
Klaus
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
...
Klaus
-
- Posts: 130
- Joined: Fri Apr 13, 2018 1:53 pm
Re: SQL Date
Perfect Klaus, you are the bestKlaus 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 ...
Thank you
Stefan
Re: SQL Date
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:
Tested and works!
Best
Klaus
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
Best
Klaus
Re: SQL Date
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
https://www.dropbox.com/s/3wpwn3hfbmpl7 ... ecode?dl=0
Re: SQL Date
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
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
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
Re: SQL Date
Nice, also looks easily extensible. Good job!
-
- Posts: 130
- Joined: Fri Apr 13, 2018 1:53 pm
Re: SQL Date
OK, I extended the select with
to get only the last 5 houres as example.
Thanks!
Stefan
Code: Select all
put 5 into zeitraum_stunde
put "select orders from torder where creation >= DATEADD(MINUTE,-"& zeitraum_stunde &",GETDATE()) into sql34
Thanks!
Stefan
Re: SQL Date
This (DATEADD) is a SQL-Server command, sure this works with SQLite?
See here: https://www.sqlite.org/lang_datefunc.html
See here: https://www.sqlite.org/lang_datefunc.html
Re: SQL Date
Hi,
in SQLite, try this:
Have fun!
in SQLite, try this:
orSELECT DATE('now','+1 day')
;-)SELECT DATETIME('now','+2 hour')
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
Re: SQL Date
Why on earth is everyone ruining my pedagogical approach!
Re: SQL Date
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 ...)
Have fun!
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 ...)
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
-
- Posts: 130
- Joined: Fri Apr 13, 2018 1:53 pm
Re: SQL Date
As I wrote in the beginning:Klaus wrote: ↑Fri Jun 22, 2018 4:39 pmThis (DATEADD) is a SQL-Server command, sure this works with SQLite?
See here: https://www.sqlite.org/lang_datefunc.html
My query is working with the lines I wrote on MS SQL Server.In my MS SQL Database i have a date field:
Re: SQL Date
Ooops, sorry, my fault!