Extracting from TimeStamp - Malformed Query (sometimes)
19 views
Skip to first unread message
Kyle Green
May 19, 2025, 5:08:32 PMMay 19
to firebird-n...@googlegroups.com
For my website, I am trying to extract user friendly dates from a
TimeStamp field. Ex: "Tue 20 May - 5:30p"
I am using a query listed below, and attached.
This query works in my asp.net web app on my Dev machine with Firebird
5. It also works in FlameRobin.
On the live server, Windows Server 2019, running Firebird 5, it runs
in FlamRobin, but it does not run in the deployed web app. Seems to be
the same Firebird 5.
It fails with a "Malformed Query' error.
It is very difficult to narrow the problem, because I have to compile
and deploy an entire web app to produce the error. And while I am
studying it, the site breaks for all users.
I am hoping someone can spot the problem., or suggest a better way to
get a formatted date
Summary:
It works on compiled Dev App, and FlameRobin
It Fails on the server on the same deployed Web App, BUT it still
works in FlameRobin
website is running: https://nevadacity.rocks
Thank you,
Kyle Green
SELECT T1.VenId, T1.VenName
,T2.CalEvId
,T2.EventStart
,T2.EventName
,T2.EventDescSummary
,T2.ImgUrl
,T4.ArtId
,T4.ArtName
,(T4.UrlPathName || T4.UrlDataName) as FullArtistPath
,case extract( weekday from T2.EventStart)
when 0 then 'Sun'
when 1 then 'Mon'
when 2 then 'Tue'
when 3 then 'Wed'
when 4 then 'Thu'
when 5 then 'Fri'
when 6 then 'Sat'
end
|| ' ' ||
extract(day from T2.EventStart)
|| ' ' ||
case extract(month from T2.EventStart)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
end
|| ' - ' ||
extract(Hour from T2.EventStart)
|| ':'||
extract(minute from T2.EventStart)
as TheEvDate
,extract(hour from T2.EventStart)
|| ':'||
extract(minute from T2.EventStart)
as TheTime
from CalEvVenue T1
join CalEvEvent T2 on T1.VenId=T2.VenId
left outer join CALEVEVENT2ARTIST T3 on T2.CalEvId=T3.CalEvId
left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId
where T2.EventStart >
'03/12/2025 00:00' and T2.ShouldDisplay = 1 order by T2.EVENTSTART;
TimeStamp field. Ex: "Tue 20 May - 5:30p"
I am using a query listed below, and attached.
This query works in my asp.net web app on my Dev machine with Firebird
5. It also works in FlameRobin.
On the live server, Windows Server 2019, running Firebird 5, it runs
in FlamRobin, but it does not run in the deployed web app. Seems to be
the same Firebird 5.
It fails with a "Malformed Query' error.
It is very difficult to narrow the problem, because I have to compile
and deploy an entire web app to produce the error. And while I am
studying it, the site breaks for all users.
I am hoping someone can spot the problem., or suggest a better way to
get a formatted date
Summary:
It works on compiled Dev App, and FlameRobin
It Fails on the server on the same deployed Web App, BUT it still
works in FlameRobin
website is running: https://nevadacity.rocks
Thank you,
Kyle Green
SELECT T1.VenId, T1.VenName
,T2.CalEvId
,T2.EventStart
,T2.EventName
,T2.EventDescSummary
,T2.ImgUrl
,T4.ArtId
,T4.ArtName
,(T4.UrlPathName || T4.UrlDataName) as FullArtistPath
,case extract( weekday from T2.EventStart)
when 0 then 'Sun'
when 1 then 'Mon'
when 2 then 'Tue'
when 3 then 'Wed'
when 4 then 'Thu'
when 5 then 'Fri'
when 6 then 'Sat'
end
|| ' ' ||
extract(day from T2.EventStart)
|| ' ' ||
case extract(month from T2.EventStart)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
end
|| ' - ' ||
extract(Hour from T2.EventStart)
|| ':'||
extract(minute from T2.EventStart)
as TheEvDate
,extract(hour from T2.EventStart)
|| ':'||
extract(minute from T2.EventStart)
as TheTime
from CalEvVenue T1
join CalEvEvent T2 on T1.VenId=T2.VenId
left outer join CALEVEVENT2ARTIST T3 on T2.CalEvId=T3.CalEvId
left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId
where T2.EventStart >
'03/12/2025 00:00' and T2.ShouldDisplay = 1 order by T2.EVENTSTART;
Colin
May 19, 2025, 5:28:52 PMMay 19
to firebird-net-provider
Have not looked into what might be the problem, but in terms of a maybe better way, you can create a function.
create or alter function DATE_MONTH_TO_SHORT_NAME (
DATE_TO_CONVERT date)
returns char(3) deterministic
as
declare variable M integer;
begin
/* Returns a string formatted to a short month name (mmm) for the given date (or null if null is passed in) */
if (date_to_convert is null) then
return null;
:M = Extract (month from date_to_convert);
return Decode(:M,
1, 'Jan',
2, 'Feb',
3, 'Mar',
4, 'Apr',
5, 'May',
6, 'Jun',
7, 'Jul',
8, 'Aug',
9, 'Sep',
10, 'Oct',
11, 'Nov',
12, 'Dec'
);
end;
Here is an example of one of ours to get you going... you obviously will have to change the format as required.
Then in the SQL, you can just go
Select date_month_to_short_name(current_date) from rdb$Database
Not sure how you familiar you are with Firebird, but just in case you were wondering about the rdb$database, rdb$database is just a table that returns one row, so you can use it to test out such things.
Here is the code to the function:
create or alter function DATE_MONTH_TO_SHORT_NAME (
DATE_TO_CONVERT date)
returns char(3) deterministic
as
declare variable M integer;
begin
/* Returns a string formatted to a short month name (mmm) for the given date (or null if null is passed in) */
if (date_to_convert is null) then
return null;
:M = Extract (month from date_to_convert);
return Decode(:M,
1, 'Jan',
2, 'Feb',
3, 'Mar',
4, 'Apr',
5, 'May',
6, 'Jun',
7, 'Jul',
8, 'Aug',
9, 'Sep',
10, 'Oct',
11, 'Nov',
12, 'Dec'
);
end;
Good luck :-)
Colin F
May 19, 2025, 5:35:41 PMMay 19
to firebird-n...@googlegroups.com
I guess this is assuming you have access to the database... maybe you don't.
If not, are you sure it is the same type of DB, and could the input data be different to what you are expecting? E.g. What happens if T2.EventStart is null?
Regards
Colin
Colin
--
Support the ongoing development of Firebird! Consider donating to the
Firebird Foundation and help ensure its future. Every contribution makes
a difference. Learn more and donate here:
https://www.firebirdsql.org/donate.
---
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/917b98fc-71f0-4e34-8ed3-61945888b358n%40googlegroups.com.
Gerdus van Zyl
May 20, 2025, 3:53:25 AMMay 20
to firebird-n...@googlegroups.com
Please post the c# code you are using to invoke the query.
Are you using sql parameters for the where clause?
eg in T2.EventStart > '03/12/2025 00:00' ?Are you using sql parameters for the where clause?
Mark Rotteveel
May 20, 2025, 3:55:23 AMMay 20
to firebird-n...@googlegroups.com
On 19/05/2025 23:08, Kyle Green wrote:
> It fails with a "Malformed Query' error.
What is the full error and stacktrace? Malformed Query is not a Firebird
> It fails with a "Malformed Query' error.
error message, so presumably it is a Firebird .NET provider exception.
Mark
--
Mark Rotteveel
Kyle Green
May 20, 2025, 3:45:58 PMMay 20
to firebird-n...@googlegroups.com
Using literals.
Here is the final part of the query as I compose it:
sbQuery.AppendLine(" from CalEvVenue T1 ");
sbQuery.AppendLine(" join CalEvEvent T2 on T1.VenId=T2.VenId ");
sbQuery.AppendLine(" left outer join CALEVEVENT2ARTIST T3 on
T2.CalEvId=T3.CalEvId ");
sbQuery.AppendLine(" left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId ");
sbQuery.AppendLine(" where T2.EventStart > ");
sbQuery.Append("'");
sbQuery.Append(dtThreeDaysAgo.ToString("MM/dd/yyyy HH:mm"));
sbQuery.Append("'");
sbQuery.Append(" and T2.ShouldDisplay = 1 ");
sbQuery.AppendLine(" order by T2.EVENTSTART; ");
////////////
Then I create a DataTable and call this function after the query
string is composed:
DataTable dt = new DataTable();
bRet = GetBroker().RunQuerySelect(sbQuery.ToString(), dt, ref sErrMsg);
/////////////////////
public bool RunQuerySelect(FbConnection LocalConnection
,string sQuery
,DataTable dt
,ref string sErrMsg
)
{
try
{
if(!TryOpenConnection(LocalConnection, ref sErrMsg))
{
return false;
}
FbDataAdapter da = new FbDataAdapter(sQuery, LocalConnection);
da.Fill(dt);
LocalConnection.Close();
} //end try
catch(FbException Ex)
{
sErrMsg += Ex.ToString();
sErrMsg += sQuery;
WriteLogAndAlert(ref sErrMsg);
return false;
} //end catch
finally
{
if( (LocalConnection.State == ConnectionState.Open) ||
(LocalConnection.State == ConnectionState.Broken) )
{
LocalConnection.Close();
}
} //end finally
return true;
> To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/CALvycTkCmSFmwZfEc%3DN%3DQZLFLcOW5geJ2ZpKn4E%3Dru%3DA4eH_dQ%40mail.gmail.com.
Here is the final part of the query as I compose it:
sbQuery.AppendLine(" from CalEvVenue T1 ");
sbQuery.AppendLine(" join CalEvEvent T2 on T1.VenId=T2.VenId ");
sbQuery.AppendLine(" left outer join CALEVEVENT2ARTIST T3 on
T2.CalEvId=T3.CalEvId ");
sbQuery.AppendLine(" left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId ");
sbQuery.AppendLine(" where T2.EventStart > ");
sbQuery.Append("'");
sbQuery.Append(dtThreeDaysAgo.ToString("MM/dd/yyyy HH:mm"));
sbQuery.Append("'");
sbQuery.Append(" and T2.ShouldDisplay = 1 ");
sbQuery.AppendLine(" order by T2.EVENTSTART; ");
////////////
Then I create a DataTable and call this function after the query
string is composed:
DataTable dt = new DataTable();
bRet = GetBroker().RunQuerySelect(sbQuery.ToString(), dt, ref sErrMsg);
/////////////////////
public bool RunQuerySelect(FbConnection LocalConnection
,string sQuery
,DataTable dt
,ref string sErrMsg
)
{
try
{
if(!TryOpenConnection(LocalConnection, ref sErrMsg))
{
return false;
}
FbDataAdapter da = new FbDataAdapter(sQuery, LocalConnection);
da.Fill(dt);
LocalConnection.Close();
} //end try
catch(FbException Ex)
{
sErrMsg += Ex.ToString();
sErrMsg += sQuery;
WriteLogAndAlert(ref sErrMsg);
return false;
} //end catch
finally
{
if( (LocalConnection.State == ConnectionState.Open) ||
(LocalConnection.State == ConnectionState.Broken) )
{
LocalConnection.Close();
}
} //end finally
return true;
Kyle Green
May 20, 2025, 4:15:23 PMMay 20
to firebird-n...@googlegroups.com
Yes, I am using .net provider. I didn't capture the trace. It was a
while back, and have been living with a 24 hour time query that works.
(the objective is to get the 12 hour time, with an a or p attached for am/pm)
The successful query produces this on the live site: "Mon 19 May -
17:30" (https:://nevadacity.rocks) (note the dot rocks)
The query that works on Dev, but not on Live produces "Mon 19 May -
5:30" (hopefully I can add a or p)
At this point, I would have to recompile the failing query and deploy
the full web app, go public with it to get the message, Then I would
have to recompile with the working one, and redeploy, so it will
continue to work for my users. (but I will do it if we cannot spot the
problem)
I have attached the code snippetsin a file. I will also paste below
(someone tell me if I should not paste so much code) Thank you - Kyle
Green
////////// select portion used for both working and failing query
/////////////////
sbQuery.AppendLine(" SELECT T1.VenId, T1.VenName ");
sbQuery.AppendLine(" ,T2.CalEvId ");
sbQuery.AppendLine(" ,T2.EventStart ");
sbQuery.AppendLine(" ,T2.EventName ");
sbQuery.AppendLine(" ,T2.EventDescSummary ");
sbQuery.AppendLine(" ,T2.ImgUrl ");
sbQuery.AppendLine(" ,T4.ArtId ");
sbQuery.AppendLine(" ,T4.ArtName ");
sbQuery.AppendLine(" ,(T4.UrlPathName || T4.UrlDataName) as
FullArtistPath "); //must add comma for query manager BROKE
sbQuery.AppendLine(" ,(T2.UrlPathName || T2.UrlDataName) as
FullEventPath "); //must add comma for query manager BROKE
sbQuery.AppendLine(" ,(T1.UrlPathName || T1.UrlDataName) as
FullVenuePath "); //must add comma for query manager BROKE
/// THIS SNIPPET WORKS on BOTH Dev and Live /////////////////////////////////
////but it gives me 24 hour time "Mon 19 May - 17:30"
StringBuilder sbDateTimeQuery = new StringBuilder();
//DateTimeQuery.Append("select ");
sbDateTimeQuery.AppendLine(",case extract( weekday from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 0 then 'Sun' ");
sbDateTimeQuery.AppendLine("when 1 then 'Mon' ");
sbDateTimeQuery.AppendLine("when 2 then 'Tue' ");
sbDateTimeQuery.AppendLine("when 3 then 'Wed' ");
sbDateTimeQuery.AppendLine("when 4 then 'Thu' ");
sbDateTimeQuery.AppendLine("when 5 then 'Fri' ");
sbDateTimeQuery.AppendLine("when 6 then 'Sat' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("extract(day from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("case extract(month from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 1 then 'Jan' ");
sbDateTimeQuery.AppendLine("when 2 then 'Feb' ");
sbDateTimeQuery.AppendLine("when 3 then 'Mar' ");
sbDateTimeQuery.AppendLine("when 4 then 'Apr' ");
sbDateTimeQuery.AppendLine("when 5 then 'May' ");
sbDateTimeQuery.AppendLine("when 6 then 'Jun' ");
sbDateTimeQuery.AppendLine("when 7 then 'Jul' ");
sbDateTimeQuery.AppendLine("when 8 then 'Aug' ");
sbDateTimeQuery.AppendLine("when 9 then 'Sep' ");
sbDateTimeQuery.AppendLine("when 10 then 'Oct' ");
sbDateTimeQuery.AppendLine("when 11 then 'Nov' ");
sbDateTimeQuery.AppendLine("when 12 then 'Dec' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' - ' || ");
sbDateTimeQuery.AppendLine("extract(Hour from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ':'|| ");
sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart) ");
//DateTimeQuery.AppendLine("--|| ', '|| ");
//DateTimeQuery.AppendLine("--extract(year from T2.EventStart) ");
sbDateTimeQuery.AppendLine("as TheEvDate ");
sbDateTimeQuery.AppendLine(",extract(hour from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ':'|| ");
sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart) ");
sbDateTimeQuery.AppendLine("as TheTime ");
//// THIS SNIPPET WORKS ON DEV, but FAILS on live. (even tho it Works
on live FlameRobin) //////////////
//// it gives me 12 hour time "Mon 19 May - 5:30"
StringBuilder sbDateTimeQuery = new StringBuilder();
sbDateTimeQuery.AppendLine(" ( case extract(month from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 1 then 'Jan' ");
sbDateTimeQuery.AppendLine("when 2 then 'Feb' ");
sbDateTimeQuery.AppendLine("when 3 then 'Mar' ");
sbDateTimeQuery.AppendLine("when 4 then 'Apr' ");
sbDateTimeQuery.AppendLine("when 5 then 'May' ");
sbDateTimeQuery.AppendLine("when 6 then 'Jun' ");
sbDateTimeQuery.AppendLine("when 7 then 'Jul' ");
sbDateTimeQuery.AppendLine("when 8 then 'Aug' ");
sbDateTimeQuery.AppendLine("when 9 then 'Sep' ");
sbDateTimeQuery.AppendLine("when 10 then 'Oct' ");
sbDateTimeQuery.AppendLine("when 11 then 'Nov' ");
sbDateTimeQuery.AppendLine("when 12 then 'Dec' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("extract(day from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("case extract(weekday from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 0 then 'Sun' ");
sbDateTimeQuery.AppendLine("when 1 then 'Mon' ");
sbDateTimeQuery.AppendLine("when 2 then 'Tue' ");
sbDateTimeQuery.AppendLine("when 3 then 'Wed' ");
sbDateTimeQuery.AppendLine("when 4 then 'Thu' ");
sbDateTimeQuery.AppendLine("when 5 then 'Fri' ");
sbDateTimeQuery.AppendLine("when 6 then 'Sat' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' - ' || ");
sbDateTimeQuery.AppendLine(" case ");
sbDateTimeQuery.AppendLine("when extract(Hour from
T2.EventStart) = 0 then 12 ");
sbDateTimeQuery.AppendLine("when extract(Hour from
T2.EventStart) > 12 then extract(Hour from T2.EventStart) -12 ");
sbDateTimeQuery.AppendLine("else extract(Hour from T2.EventStart) ");
sbDateTimeQuery.AppendLine(" end ");
sbDateTimeQuery.AppendLine("|| ':'|| ");
sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart)
"); //want to add am or pm
sbDateTimeQuery.AppendLine(" || ' - ' || ");
sbDateTimeQuery.AppendLine(" T2.EventName ");
sbDateTimeQuery.AppendLine(" ) as NameDate ");
//as TheDate
//////// This is the from and where used for both versions /////////////
sbQuery.AppendLine(" from CalEvVenue T1 ");
sbQuery.AppendLine(" join CalEvEvent T2 on T1.VenId=T2.VenId ");
sbQuery.AppendLine(" left outer join CALEVEVENT2ARTIST T3 on
T2.CalEvId=T3.CalEvId ");
sbQuery.AppendLine(" left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId ");
sbQuery.AppendLine(" where T2.EventStart > ");
sbQuery.Append("'");
sbQuery.Append(dtThreeDaysAgo.ToString("MM/dd/yyyy HH:mm"));
sbQuery.Append("'");
sbQuery.Append(" and T2.ShouldDisplay = 1 ");
sbQuery.AppendLine(" order by T2.EVENTSTART; ");
//end doc
while back, and have been living with a 24 hour time query that works.
(the objective is to get the 12 hour time, with an a or p attached for am/pm)
The successful query produces this on the live site: "Mon 19 May -
17:30" (https:://nevadacity.rocks) (note the dot rocks)
The query that works on Dev, but not on Live produces "Mon 19 May -
5:30" (hopefully I can add a or p)
At this point, I would have to recompile the failing query and deploy
the full web app, go public with it to get the message, Then I would
have to recompile with the working one, and redeploy, so it will
continue to work for my users. (but I will do it if we cannot spot the
problem)
I have attached the code snippetsin a file. I will also paste below
(someone tell me if I should not paste so much code) Thank you - Kyle
Green
////////// select portion used for both working and failing query
/////////////////
sbQuery.AppendLine(" SELECT T1.VenId, T1.VenName ");
sbQuery.AppendLine(" ,T2.CalEvId ");
sbQuery.AppendLine(" ,T2.EventStart ");
sbQuery.AppendLine(" ,T2.EventName ");
sbQuery.AppendLine(" ,T2.EventDescSummary ");
sbQuery.AppendLine(" ,T2.ImgUrl ");
sbQuery.AppendLine(" ,T4.ArtId ");
sbQuery.AppendLine(" ,T4.ArtName ");
sbQuery.AppendLine(" ,(T4.UrlPathName || T4.UrlDataName) as
FullArtistPath "); //must add comma for query manager BROKE
sbQuery.AppendLine(" ,(T2.UrlPathName || T2.UrlDataName) as
FullEventPath "); //must add comma for query manager BROKE
sbQuery.AppendLine(" ,(T1.UrlPathName || T1.UrlDataName) as
FullVenuePath "); //must add comma for query manager BROKE
/// THIS SNIPPET WORKS on BOTH Dev and Live /////////////////////////////////
////but it gives me 24 hour time "Mon 19 May - 17:30"
StringBuilder sbDateTimeQuery = new StringBuilder();
//DateTimeQuery.Append("select ");
sbDateTimeQuery.AppendLine(",case extract( weekday from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 0 then 'Sun' ");
sbDateTimeQuery.AppendLine("when 1 then 'Mon' ");
sbDateTimeQuery.AppendLine("when 2 then 'Tue' ");
sbDateTimeQuery.AppendLine("when 3 then 'Wed' ");
sbDateTimeQuery.AppendLine("when 4 then 'Thu' ");
sbDateTimeQuery.AppendLine("when 5 then 'Fri' ");
sbDateTimeQuery.AppendLine("when 6 then 'Sat' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("extract(day from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("case extract(month from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 1 then 'Jan' ");
sbDateTimeQuery.AppendLine("when 2 then 'Feb' ");
sbDateTimeQuery.AppendLine("when 3 then 'Mar' ");
sbDateTimeQuery.AppendLine("when 4 then 'Apr' ");
sbDateTimeQuery.AppendLine("when 5 then 'May' ");
sbDateTimeQuery.AppendLine("when 6 then 'Jun' ");
sbDateTimeQuery.AppendLine("when 7 then 'Jul' ");
sbDateTimeQuery.AppendLine("when 8 then 'Aug' ");
sbDateTimeQuery.AppendLine("when 9 then 'Sep' ");
sbDateTimeQuery.AppendLine("when 10 then 'Oct' ");
sbDateTimeQuery.AppendLine("when 11 then 'Nov' ");
sbDateTimeQuery.AppendLine("when 12 then 'Dec' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' - ' || ");
sbDateTimeQuery.AppendLine("extract(Hour from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ':'|| ");
sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart) ");
//DateTimeQuery.AppendLine("--|| ', '|| ");
//DateTimeQuery.AppendLine("--extract(year from T2.EventStart) ");
sbDateTimeQuery.AppendLine("as TheEvDate ");
sbDateTimeQuery.AppendLine(",extract(hour from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ':'|| ");
sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart) ");
sbDateTimeQuery.AppendLine("as TheTime ");
//// THIS SNIPPET WORKS ON DEV, but FAILS on live. (even tho it Works
on live FlameRobin) //////////////
//// it gives me 12 hour time "Mon 19 May - 5:30"
StringBuilder sbDateTimeQuery = new StringBuilder();
sbDateTimeQuery.AppendLine(" ( case extract(month from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 1 then 'Jan' ");
sbDateTimeQuery.AppendLine("when 2 then 'Feb' ");
sbDateTimeQuery.AppendLine("when 3 then 'Mar' ");
sbDateTimeQuery.AppendLine("when 4 then 'Apr' ");
sbDateTimeQuery.AppendLine("when 5 then 'May' ");
sbDateTimeQuery.AppendLine("when 6 then 'Jun' ");
sbDateTimeQuery.AppendLine("when 7 then 'Jul' ");
sbDateTimeQuery.AppendLine("when 8 then 'Aug' ");
sbDateTimeQuery.AppendLine("when 9 then 'Sep' ");
sbDateTimeQuery.AppendLine("when 10 then 'Oct' ");
sbDateTimeQuery.AppendLine("when 11 then 'Nov' ");
sbDateTimeQuery.AppendLine("when 12 then 'Dec' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("extract(day from T2.EventStart) ");
sbDateTimeQuery.AppendLine("|| ' ' || ");
sbDateTimeQuery.AppendLine("case extract(weekday from T2.EventStart) ");
sbDateTimeQuery.AppendLine("when 0 then 'Sun' ");
sbDateTimeQuery.AppendLine("when 1 then 'Mon' ");
sbDateTimeQuery.AppendLine("when 2 then 'Tue' ");
sbDateTimeQuery.AppendLine("when 3 then 'Wed' ");
sbDateTimeQuery.AppendLine("when 4 then 'Thu' ");
sbDateTimeQuery.AppendLine("when 5 then 'Fri' ");
sbDateTimeQuery.AppendLine("when 6 then 'Sat' ");
sbDateTimeQuery.AppendLine("end ");
sbDateTimeQuery.AppendLine("|| ' - ' || ");
sbDateTimeQuery.AppendLine(" case ");
sbDateTimeQuery.AppendLine("when extract(Hour from
T2.EventStart) = 0 then 12 ");
sbDateTimeQuery.AppendLine("when extract(Hour from
T2.EventStart) > 12 then extract(Hour from T2.EventStart) -12 ");
sbDateTimeQuery.AppendLine("else extract(Hour from T2.EventStart) ");
sbDateTimeQuery.AppendLine(" end ");
sbDateTimeQuery.AppendLine("|| ':'|| ");
sbDateTimeQuery.AppendLine("extract(minute from T2.EventStart)
"); //want to add am or pm
sbDateTimeQuery.AppendLine(" || ' - ' || ");
sbDateTimeQuery.AppendLine(" T2.EventName ");
sbDateTimeQuery.AppendLine(" ) as NameDate ");
//as TheDate
//////// This is the from and where used for both versions /////////////
sbQuery.AppendLine(" from CalEvVenue T1 ");
sbQuery.AppendLine(" join CalEvEvent T2 on T1.VenId=T2.VenId ");
sbQuery.AppendLine(" left outer join CALEVEVENT2ARTIST T3 on
T2.CalEvId=T3.CalEvId ");
sbQuery.AppendLine(" left outer join CalEvArtist T4 on T4.ArtId=T3.ArtId ");
sbQuery.AppendLine(" where T2.EventStart > ");
sbQuery.Append("'");
sbQuery.Append(dtThreeDaysAgo.ToString("MM/dd/yyyy HH:mm"));
sbQuery.Append("'");
sbQuery.Append(" and T2.ShouldDisplay = 1 ");
sbQuery.AppendLine(" order by T2.EVENTSTART; ");
> --
> Support the ongoing development of Firebird! Consider donating to the
> Firebird Foundation and help ensure its future. Every contribution makes
> a difference. Learn more and donate here:
> https://www.firebirdsql.org/donate.
> ---
> You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
> To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/0b37552b-b432-406d-8de7-85cb70cd5795%40lawinegevaar.nl.
> Support the ongoing development of Firebird! Consider donating to the
> Firebird Foundation and help ensure its future. Every contribution makes
> a difference. Learn more and donate here:
> https://www.firebirdsql.org/donate.
> ---
> You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
Kyle Green
May 20, 2025, 4:22:34 PMMay 20
to firebird-n...@googlegroups.com
Colin F said: I guess this is assuming you have access to the
near as I can tell theta are exactly the same versions. The logs show
the query is complete with no missing or null or empty values. db rule
do not allow a null EventStart date.
This db has over 300 tables and is strictly normalized. I use
parameterized queries in most cases. But not in this case so far.
> To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/CAOdJ4-ncvnTWxwjqXC_7T8YCDgCXOKAA%3DVRNfk5dJzcwMAACJQ%40mail.gmail.com.
database... maybe you don't. If not, are you sure it is the same type
of DB, and could the input data be different to what you are
expecting? E.g. What happens if T2.EventStart is null?
Kyle: I installed Firebird 5 on both machines around the same time, as
of DB, and could the input data be different to what you are
expecting? E.g. What happens if T2.EventStart is null?
near as I can tell theta are exactly the same versions. The logs show
the query is complete with no missing or null or empty values. db rule
do not allow a null EventStart date.
This db has over 300 tables and is strictly normalized. I use
parameterized queries in most cases. But not in this case so far.
Kyle Green
May 20, 2025, 4:34:49 PMMay 20
to firebird-n...@googlegroups.com
Possibly I should change my approach to this problem
Rather than untangle my work as it is - Should I start another thread
that simply asks how to get the following string from a Timestamp
field?
The objective is to get the 12 hour time, with an a or p attached for am/pm:
"Mon 19 May - 5:30p"
Maybe there are some time tested examples I could follow, instead of
reverse engineering what I have done, which seems to be problematic?
Let me know if I should do this,
Thanks,
Kyle Green
> To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/CALvycTkCmSFmwZfEc%3DN%3DQZLFLcOW5geJ2ZpKn4E%3Dru%3DA4eH_dQ%40mail.gmail.com.
Rather than untangle my work as it is - Should I start another thread
that simply asks how to get the following string from a Timestamp
field?
The objective is to get the 12 hour time, with an a or p attached for am/pm:
"Mon 19 May - 5:30p"
Maybe there are some time tested examples I could follow, instead of
reverse engineering what I have done, which seems to be problematic?
Let me know if I should do this,
Thanks,
Kyle Green
Gerdus van Zyl
May 21, 2025, 4:19:14 AMMay 21
to firebird-net-provider
Have you tested on dev with a backup from the live?
if it works in dev on the same data then the data is not the problem and there is a difference in setup eg. firebird config, windows timezone, windows regional settings, etc.
You can also try running the query using the lower level ADO FbCommand/ExecuteReader, if it works there I think the problem is with using Datatables. I always had nothing but problems with datatables/DataAdapter.
if it works in dev on the same data then the data is not the problem and there is a difference in setup eg. firebird config, windows timezone, windows regional settings, etc.
You can also try running the query using the lower level ADO FbCommand/ExecuteReader, if it works there I think the problem is with using Datatables. I always had nothing but problems with datatables/DataAdapter.
If the question is only about the SQL the AM/PM is a simple extension of what you have already done.
Example:
with T2 as (
select cast('2025-05-21 17:31' as timestamp) as EventStart,'NevadaRocks At Night' as EventName from rdb$database
union all
select cast('2025-05-21 10:31' as timestamp) as EventStart,'NevadaRocks In The Mornin' as EventName from rdb$database
)
select
( case extract(month from T2.EventStart)
select cast('2025-05-21 17:31' as timestamp) as EventStart,'NevadaRocks At Night' as EventName from rdb$database
union all
select cast('2025-05-21 10:31' as timestamp) as EventStart,'NevadaRocks In The Mornin' as EventName from rdb$database
)
select
( case extract(month from T2.EventStart)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
end
|| ' ' ||
extract(day from T2.EventStart)
|| ' ' ||
case extract(weekday from T2.EventStart)
extract(day from T2.EventStart)
|| ' ' ||
case extract(weekday from T2.EventStart)
when 0 then 'Sun'
when 1 then 'Mon'
when 2 then 'Tue'
when 3 then 'Wed'
when 4 then 'Thu'
when 5 then 'Fri'
when 6 then 'Sat'
end
|| ' - ' ||
case
case
when extract(Hour from T2.EventStart) = 0 then 12
when extract(Hour from T2.EventStart) > 12 then extract(Hour from T2.EventStart) -12
else extract(Hour from T2.EventStart)
end
|| ':' ||
extract(minute from T2.EventStart)
|| ' ' ||
case
when extract(Hour from T2.EventStart) > 12 then 'PM'
else 'AM'
end
||
' - ' || T2.EventName
) as NameDate
from T2
end
|| ':' ||
extract(minute from T2.EventStart)
|| ' ' ||
case
when extract(Hour from T2.EventStart) > 12 then 'PM'
else 'AM'
end
||
' - ' || T2.EventName
) as NameDate
from T2
result:
May 21 Wed - 5:31 PM - NevadaRocks At Night
May 21 Wed - 5:31 PM - NevadaRocks At Night
May 21 Wed - 10:31 AM - NevadaRocks In The Mornin
Kyle Green
May 22, 2025, 8:23:42 PMMay 22
to firebird-n...@googlegroups.com
Thank you Gerdus for your insights. I will try to follow your
suggestions and will let you know.
As a further refinement to my problem, for better context, the version
of the query that works on Live avoids trying to get the 12 hour time,
and I am running the site with 24 hour time. When I try to implement
the 12 hour time is when I get the "malformed Query" message (but only
on live.)
Thanks,
Kyle Green
suggestions and will let you know.
As a further refinement to my problem, for better context, the version
of the query that works on Live avoids trying to get the 12 hour time,
and I am running the site with 24 hour time. When I try to implement
the 12 hour time is when I get the "malformed Query" message (but only
on live.)
Thanks,
Kyle Green
> --
> Support the ongoing development of Firebird! Consider donating to the
> Firebird Foundation and help ensure its future. Every contribution makes
> a difference. Learn more and donate here:
> https://www.firebirdsql.org/donate.
> ---
> You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
> To view this discussion visit https://groups.google.com/d/msgid/firebird-net-provider/e2750184-7537-4077-ab6a-1f7e8925626fn%40googlegroups.com.
> Support the ongoing development of Firebird! Consider donating to the
> Firebird Foundation and help ensure its future. Every contribution makes
> a difference. Learn more and donate here:
> https://www.firebirdsql.org/donate.
> ---
> You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
Jiří Činčura
May 27, 2025, 11:32:40 AMMay 27
to 'Mr. John' via firebird-net-provider
> What is the full error and stacktrace? Malformed Query is not a Firebird
> error message, so presumably it is a Firebird .NET provider exception.
No such exception exists in FirebirdClient's code, at least AFAIK. Could it maybe be "Malformed string" from server?
> error message, so presumably it is a Firebird .NET provider exception.
--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/
Reply all
Reply to author
Forward
0 new messages