Getting different time from Database in neolane.

[nidhiv77838491]

Hi all,

When i am trying fetch the hr from the database in neolane delivery subject line, it is coming 1:00 AM where as in Database it is 5 AM in CET which is in database.

How can make it in sink with database. Please help and suggest.

Regards,

Nidhi

[Jean-Serge_Biro]

Hi Nidhi,

What function do you use in your Delivery?

In order to manage easily dates related to different timezones for readers being in different countries, you should use the Adobe Campaign Javascript API function formatDate(date, format, timezone) (where timezone is optional parameter), rather pure JavaScript functions (date.toString() etc).

For instance, in your object field:

<%= formatDate(recipient.tsBirth, "%2D %2Bl %2H") %>

See your JSAPI documentation for the different format options.

Regards
J-Serge

[nidhiv77838491]

Hi Jean-Serge Biron​,

Whatever hour i am getting, i need to subtract -1 from the hr i.e. if date along with time 01-09-2017 02:23:23 i need to show 01-09-2017 01:23:23. Can you please let me out in the same. how should i proceed with the same. Using gethour function. will it make sense to do gethour()-1;

[Jean-Serge_Biro]

Hi nidhiv,

Please may you share more details?
Your Javascript code: do you use GetDate, or date through queryDef, etc.

The data type of the date field in your XML schema and in your DB
The timezone of your server is it the same than your DB server.

Date time management is tricky, I must admit.

Is there someone else that can give some ideas/advices?

Regards
J-Serge

[nidhiv77838491]

Hi Jean-Serge Biron​,

My approach to fetch the date using new Date in workflow using targetData. But i was unable to determine why it was not picking up the time although date was coming perfectly fine. It was showing 2 everytime along with date. So i tried using queryDef, then i get to know it was showing timezone instead of time. As in database, it was date type column as well as in neolane schema. When i changed , its date type to datetime. It was working fine .

I would like to know - During day light saving, will dates and time impacted with the same? If so, then how we should handle the time for different countries having vast difference in time i.e. US , Russia. Can please suggest on the same.

Regards,

Nidhi

[Jean-Serge_Biro · Accepted solution]

Hi Nidhi,

Yes, it is really tricky, and all people struggle with that kind of issue, not only in Adobe Campaign area, but all IT areas.

In AC Javascript activity, please note that if you user new date() you get the applicative server hour/timezone for workflows but for webApp the webServer is not usually the same as applicative server (often it is located on the MTA server). And when you use GetDate() the hour/timezone returned comes from the database server itself (the database instance actually, not the machine).
So you can get different hours/time depending where are your applicative server, your web servers/MTA, and your database instance/server.
You should compare the datetime of the 3 servers (2 AC servers and the DB instance) in your case.

Then I recommend using GetDate (and other functions) because it is the same function that you can use in queryDef, Query activity.

If you have a doubt -or want to better understand) on hour/datetime functions implementations, in Query activity you can display the SQL command generated.
And you can have a look also to the Adobe Campaign generated SQL functions as SQL User functions depending on your DB engine. For instance for SQL Server, Adobe Campaign transforms the DateOnly to this User Function:

So by looking at the underlying SQL User function, you can better understand what you get, when you develop with DB oriented datetime functions (instead of Javascript ones).

Have a nice week-end.
Regards
J-Serge

[nidhiv77838491]

Hi Jean-Serge Biron​,

I would like to ask 1 doubt regarding the time in day light saving. In case of Workflows, we need to make substraction or addition in the time used to fetch from database due to time variation of database as well as neolane. Also each market having different time zone. Can you please let me know the work around to resolve the same like we need not do handle day light saving time as well as normal time separately by doing addition/subtraction . Please suggest optimize solution to it

Thanks Much in Advance,

Nidhi

[Jean-Serge_Biro]

Hi Nidhi,

Please may you tell us which elements time format you have in your schema? And please check also the underlying SQL data type.

And also all your servers time and timezone settings.

Actually, normally, the daylight time saving is correctly managed by both servers (applicative and MTA) and database layer and its own server, and so by Adobe Campaign software, if the data type for datetime elements have been set to multi-zone.

Hence, please check that carefully in the Adobe Campaigns options.

But sometimes it can happen that the problem arises if there are not synchronised time to worldwide clock (NTP protocol) or bad time settings).

If the Adobe Campaign options have not been set to multi-zone, please ask for technical operation to update/migrate your DB, it would far more convenient for you. But in the meanwhile, I don't see other solutions than manual and local calculations

Regards
J-Serge

[nidhiv77838491]

Hi Jean-Serge Biron​,

I am trying to use the enrichment in which using the TruncDateTZ but asking for three parameter i.e. date,second and timezone. Can youplease suggest whether i need to provde hard card value in the  same i.e. timezone as well as second. Kindly suggest.

Regards,

nidhi