Date conversion syntax....

Coding + IDE + Code-Editor + Syntax + User-Functions + Anything else

Date conversion syntax....

Postby GaryVass » Fri Jul 06, 2012 2:33 am

I have this code (from a kbasic program, that worked), but not in Q7Basic.... what do I need to change?

toDate = Year(sdteTo) + "/" + Month(sdteTo) + "/" + Day(sdteTo)

I need to get a date (in toDate) from the strings, as shown.

I keep getting a syntax error at the moment. I have tried to Dim toDate as String, and as TimeDate.

Any suggestions?

Thanks.
GaryVass
 
Posts: 109
Joined: Wed May 04, 2011 8:03 pm

Re: Date conversion syntax....

Postby Henning » Fri Jul 06, 2012 11:39 am

Maybe like this from "Language Reference".

Code: Select all
Function DateSerial(Year As Integer, Month As Integer, Day As Integer) As DateTime
Converts a date given by year, month and day into a big number.

Dim Date1 As DateTime
Date1 = DateSerial(1969, 2, 12) ' return Date1

Or as:
Function DateValue(String) As DateTime
Converts a date given in a string into a date type.

Dim Date1 As DateTime
Date1 = DateValue("1979-02-03")


/Henning
Henning
 
Posts: 522
Joined: Fri Aug 13, 2010 6:29 pm

Re: Date conversion syntax....

Postby GaryVass » Fri Jul 06, 2012 7:24 pm

interesting, but not quite what I wanted to do. I have files that are sent with the filename as a date... that is looking like 20121010 and I need to have 10/10/2012 to enter into the database.

That is what my code did in Kbasic, but gives errors in Q7Basic.
GaryVass
 
Posts: 109
Joined: Wed May 04, 2011 8:03 pm

Re: Date conversion syntax....

Postby Henning » Sat Jul 07, 2012 12:24 am

If it always have that format:

Code: Select all
Public Function DateConvert(d As String) As String
  Dim s As String
  s= Right(d, 2) & "/"
  s = s & Mid(d, 4, 2) & "/"
  s = s & Left(d, 4)
  Return s
End Function


/Henning
Henning
 
Posts: 522
Joined: Fri Aug 13, 2010 6:29 pm

Re: Date conversion syntax....

Postby GaryVass » Fri Mar 29, 2013 3:45 am

OK,

Assuming that it is a format problem, how do I get it to format to international? I have tried the FormatDateTime(DateTime, Format As String = "yyyy-MM-dd hh:mm:ss") As String but the function is grayed out and gives a syntax error.

Is formatdatetime working yet?

my variable, sdteTo is retreived (or will be) from a text box on a form that is inputed by the user.
GaryVass
 
Posts: 109
Joined: Wed May 04, 2011 8:03 pm

Re: Date conversion syntax....

Postby Slowdown » Fri Mar 29, 2013 11:07 am

Hi Gary,

Code: Select all
Event Init()
  Dim MyDate As String

  MyDate = FormatDateTime(Now, "MM-yyyy-dd hh:mm:ss")
  MsgBox(MyDate)
End Event

DateTime.png
DateTime.png (10.12 KiB) Viewed 2066 times

and,
Code: Select all
Event Init()
  Dim MyDate As String

  MyDate = FormatDateTime(Now, "MM-yyyy-dd")
  MsgBox(MyDate)
End Event

DateTime2.png
DateTime2.png (9.43 KiB) Viewed 2066 times

So to answer your question,
Is formatdatetime working yet?

Yes it does ;)
Kind regards,
Slowdown

OSX 10.8.x, VBox Xp, Ubuntu 32/64 bit, EOS 64 bit
Slowdown
 
Posts: 464
Joined: Sat May 01, 2010 7:11 pm
Location: Netherlands

Re: Date conversion syntax....

Postby Henning » Fri Mar 29, 2013 12:06 pm

But... all DB-engines I've tested are happier using the international date format 2013-03-29.

How a date/time is visualized is just for looks. Internally it is stored as a floating point number. Whole numbers are Date, decimals are Time.

/Henning
Henning
 
Posts: 522
Joined: Fri Aug 13, 2010 6:29 pm

Re: Date conversion syntax....

Postby Slowdown » Fri Mar 29, 2013 1:29 pm

But... all DB-engines I've tested are happier using the international date format 2013-03-29.

In that case are my examples not correct chosen in this case use,
Code: Select all
 MyDate = FormatDateTime(Now, "yyyy-MM-dd")

This date is probably internally stored as a Unix Timestamp.
for 2013-03-29 00:00:00 it's TIME STAMP is 1364533200 8-)
No need to mention that 'Now' is the pressent date and time.
Kind regards,
Slowdown

OSX 10.8.x, VBox Xp, Ubuntu 32/64 bit, EOS 64 bit
Slowdown
 
Posts: 464
Joined: Sat May 01, 2010 7:11 pm
Location: Netherlands

Re: Date conversion syntax....

Postby GaryVass » Thu May 16, 2013 1:19 pm

Ok, back to an old issue here....

I have found that I can not filter based on datevalues in mysql (at least my sql statements never do )... so I figured I would convert all my date in mysql to serial date values, then run my sql queries based on those.

I need to go the the highest date value then move back day by day. ie, serialdate - 1.

In Q7basic, I am having trouble converting my dates (sdteTo dim'd as Date) to serial dates. I have the following code where I convert portions of sdteTo to date interger values to use in the DateSerial function.... however, it does not like the conversion lines tYear = Year(sdteTo)???? The tYear, tMonth, tDay are dim'd as Integers, sdteTo as Date... (currently working in Linux).

If chkDate = 1 Then sdteTo = tCurdate
tYear = Year(sdteTo)
tDay = Day(sdteTo)
tMonth = Month(sdteTo)
strSql = "SELECT * FROM prices WHERE TICKER = '" & findit & "', DATE = '" & sdteTo & "' ORDER by DATE DESC;"
sRecordsId = Records.Open(gsDatabase, strSql)
rslt = Records.First(sRecordsId)
If rslt <> True Then
tDateChange = -1
tSerialDate = DateSerial(tYear, tMonth, tDay)
sdteTo = DateAdd("d", tDateChange, sdteTo)
strSql = "SELECT * FROM prices WHERE TICKER = '" & findit & "', DATE = '" & sdteTo & "' ORDER by DATE DESC;"
sRecordsId = Records.Open(gsDatabase, strSql)
rslt = Records.First(sRecordsId)
End If
GaryVass
 
Posts: 109
Joined: Wed May 04, 2011 8:03 pm

Re: Date conversion syntax....

Postby Henning » Thu May 16, 2013 2:23 pm

All following are valid date queries:
mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

Warning, DATE is a reserved word in MySQL!! Don't use it as fieldnames.

/Henning
Henning
 
Posts: 522
Joined: Fri Aug 13, 2010 6:29 pm

Next

Return to Questions & Answers

Who is online

Users browsing this forum: No registered users and 1 guest

cron