Forums (Obsolete)

Certified Developer v7

lucyht - 6/28/2012 1:06:37 PM

Date format of macro expressions and safe use in SQL where clause

Hi there,

I have come across a problem with the date format of the string returned from macros when I am using them in a where clause. This may be by design, but I cannot find any documentation on what determines the date format of macro expression so I would appreciated some clarity.

- So we have web and database servers (Server 2008) with region set to English (United Kingdom).
- Kentico CMS version v6.0.36 (SP1)‎.
- Kentico web site default culture set to en-UK and user culture set to en-UK.
- The database default language and the language in the connection string in the web.config are set to English NOT British English.

I was trying to use this macro expression in my Where clause on a repeater to return documents created between the user being created and their LastLogon date.
(DocumentCreatedWhen > '{%CurrentUser.UserCreated%} returns the string '28/03/2012 14:53:43' (same format for UserCreated as well) which then fails with the error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Or to put it another way the database was expecting a US format date and was supplied a UK format date.

So please could someone confirm which setting determines the date format that is returned from macro expressions. Also please could there be some documentation to clarify how you should deal with date formats in an installation with multiple sites with different cultures and users from a variety of cultures - there is only one web.config after all!

I have played safe and written database functions which I am using in my Where clause for the moment until I know for sure what determines the format of the macro expression.

Some clarity on this would be much appreciated.

lucyht


Kentico Developer	kentico_ivanat - 7/1/2012 2:24:33 PM RE:Date format of macro expressions and safe use in SQL where clause Hi, it is possible that this is a bug in the system. I will report it to our development team. In case it is really a bug it should be fixed until next Friday. Best regards, Ivana Tomanickova

Kentico Developer

kentico_ivanat - 7/4/2012 12:34:56 AM

RE:Date format of macro expressions and safe use in SQL where clause

Hi,

finally it is not a bug.

1. Into the where condition must be inserted data in database collation, in your case in us culture. From our side we cannot parse all strings and search for dates and convert them. It would not be effective.

2. But there is an easy solution. You can convert dates easily using:

{%CurrentUser.LastLogon|(culture)en-us%}

Best regards,
Ivana Tomanickova

Certified Developer v7

lucyht - 7/4/2012 3:27:28 AM

RE:Date format of macro expressions and safe use in SQL where clause

Many thanks for investigating this Invana and for the macro parameter solution.

One last question though. What is it that is determining the date format of the macro expression? The options that I can see are the region of the web server OS, the culture of the document or the preferred culture of the logged in user. It would be really useful to know what drives the date format.

Regards,
Lucy

Kentico Developer

kentico_ivanat - 7/6/2012 10:03:59 PM

RE:Date format of macro expressions and safe use in SQL where clause

Hi,

the format of date depends on Preferred UI culture: Go to edit user (CMS Desk -> Administration -> Users -> edit the user account you are using) and change 'Preferred user interface culture' to for example British English (you can make the same setting in CMS Desk -> My desk -> My profile).

Now I you would sign out and sign in back for the changes to take effect (in CMS Desk).

The date format on the live site is changed according to the site culture. This culture is set in Site Manager -> Sites -> edit your site -> Default content culture.

Plus, you may need to add this key to your web.config file in case you would like to change default UI culture.

<add key="CMSDefaultUICulture" value="en-gb"/>

Best regards,
Ivana Tomanickova


Certified Developer v7	lucyht - 7/7/2012 3:40:51 AM RE:Date format of macro expressions and safe use in SQL where clause Many thanks for the reply clarifying that. I will definitely make sure that macro dates used in SQL expressions are formatted to match the SQL connection in future! Regards, Lucy