Safe way to pass a date time value to string in a query

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
donaldshimoda
Posts: 13
Joined: Thu 16 Aug 2018 15:54

Safe way to pass a date time value to string in a query

Post by donaldshimoda » Wed 25 Mar 2020 06:18

I need to include in a query a date time value.
Need it works on any datetime format, in windows and linux.

Sample

where adate = ''+ dateformated+''

Some regional meetings have mm/dd/yy and another have dd/mm/yy

Theres any way to know which is the expect date format string for postgres server? some way to interrogate the server?

Or any other suggestion?

Bets regards

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: Safe way to pass a date time value to string in a query

Post by ertank » Wed 25 Mar 2020 10:12

Hello,

It is not known your database and version.

There is an SQL standard for date time values.

yyyy-mm-dd hh:mm:ss

It supposed to work in all cases if you format your datetime always like above. That is valid for FirebirdSQL, PostgreSQL, MySQL, SQL Server at least.

SQLite it works if you also insert your records like in above format as SQLite does not have any specific datetime data type.

donaldshimoda
Posts: 13
Joined: Thu 16 Aug 2018 15:54

Re: Safe way to pass a date time value to string in a query

Post by donaldshimoda » Wed 25 Mar 2020 11:58

Thanks will try.

Do you mean

'yyyy-mm-dd hh:nn:ss'

rigth?

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: Safe way to pass a date time value to string in a query

Post by ertank » Wed 25 Mar 2020 12:30

I meant to format your date/date time like that and pass as if it is a string parameter (with single quotes)

Today is 25th of March, 2020 15:29

That should be formatted as
2020-03-25 15:29:00

And passed as a string parameter to query.

That format normally accepted by common sql servers. Almost all of them

donaldshimoda
Posts: 13
Joined: Thu 16 Aug 2018 15:54

Re: Safe way to pass a date time value to string in a query

Post by donaldshimoda » Wed 25 Mar 2020 12:34

Exactly, but the format to convert is that

'yyyy-mm-dd hh:nn:ss'

and not that you write

yyyy-mm-dd hh:mm:ss

for that reason I pointed out to serve other read it in a future.

Thanks
best regards.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Safe way to pass a date time value to string in a query

Post by MaximG » Thu 30 Apr 2020 08:11

Ertrank is right. When passing the date-time parameter as a string, database server settings will be applied rather than the local system settings. The 'yyyy-mm-dd hh:nn:ss' format is supported in many database servers.

Post Reply