SQL Azure – Adjusting for different timezones a practical example

Here I create a main table which can be used to translate times into a UTC value using a function I store here. It should be noted that if you have a field with variable type set to datetime and you set the default to GetDate() – the server will create a UTC value on record creation but time is set on the server and with SQL Azure you don’t have an option to change it.

CREATE TABLE [dbo].[MainTable](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[storeddatetime] [datetime] NULL,
	[timezonename] [nvarchar](128) NULL
PRIMARY KEY CLUSTERED 
(
	[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MainTable] ADD  CONSTRAINT [DF_MainTable_storeddatetime]  DEFAULT (getdate()) FOR [storeddatetime]
GO

Next we create a timezone table

CREATE TABLE [dbo].[TimeZoneTable](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[timezonename] [nvarchar](128) NULL,
PRIMARY KEY CLUSTERED 
(
	[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And we can populate this from sys.time_zone_info table.

INSERT INTO TimeZoneTable (timezonename)
SELECT name FROM sys.time_zone_info;

We can create a function to convert this datetime value knowing the timezone into UTC is as follows

CREATE FUNCTION dbo.fn_ConvertToUTC (@localDatetime DATETIME, @timeZone NVARCHAR(128))
RETURNS DATETIME
AS
BEGIN
    DECLARE @utcDatetime DATETIME;
    SET @utcDatetime = @localDatetime AT TIME ZONE @timeZone AT TIME ZONE 'UTC';
    RETURN @utcDatetime;
END;

Now runnig the following query on some example data we get the following

SELECT 
    pkid, 
    storeddatetime, 
    timezonename, 
    dbo.fn_ConvertToUTC(storeddatetime, timezonename) AS utcValue
FROM 
    MainTable;

And here is a function that will convert to local time from utc

CREATE FUNCTION dbo.fn_ConvertToLocal (@utcDatetime DATETIME, @timeZone NVARCHAR(128))
RETURNS DATETIME
AS
BEGIN
    DECLARE @localDatetime DATETIME;
    SET @localDatetime = @utcDatetime AT TIME ZONE 'UTC' AT TIME ZONE @timeZone;
    RETURN @localDatetime;
END;

Javascript option
The getTimezoneOffset() method of the Date object in JavaScript calculates the difference, in minutes, between the local time zone and the UTC time zone 1. The method returns a positive value if the local time zone is behind UTC, and a negative value if the local time zone is ahead of UTC 1.

The method works by subtracting the local time zone offset from UTC time. For example, if the local time zone is 5 hours behind UTC, then the method will return 300 (5 * 60) 2

PHPRunner – Pass Value to CSS Reference using Javascript in a pop up

Javascript is very powerful and will easily calculate all sorts of interesting things for you dynamically. In PHPRunner I use the popup windows for nearly every table or view form so I wanted it to work with these.

But with PHPRunner we want to store these in the database. I had a devilish time finding a way of referencing the field with which to copy any Javascript value into. After some lengthy discussion with ChatGPT 4 (via Bing) it suggested that I might try and use the CSS Selector.

I then discovered that I couldn’t seem to identify the name of the CSS Selector.

What I discovered is that I could not see a static CSS Selector reference for any of the fields except those that I had altered the formatting on for example changing the font to Roboto Mono.

So first step choose your target field and then alter it using the PHP page designer and then publish.

What I discovered was after that I could use the inspect item to identify the CSS Selector

On the published application navigate to the form and the field you wish to target for entry right click and select inspect.

Look to the DevTools window (in chrome and you should see in bold the css names of your field

You can then ask ChatGPT the following

Can you parse the input css selector I need from the following string that can be used by javascipt to be passed a value

[data-page="t0017_add"][data-itemid="integrated_edit_field4"][data-page="t0017_add"][data-itemid="integrated_edit_field4"][data-page="t0017_add"][data-itemid="integrated_edit_field4"][data-page="t0017_add"][data-itemid="integrated_edit_field4"] > * > * > input

An element is a part of a webpage. In XML and HTML, an element may contain a data item or a chunk of text or an image, or perhaps nothing. A typical element includes an opening tag with some attributes, enclosed text content, and a closing tag. Elements and tags are not the same things.

More on elements is available here

This can now be used behind a button and away we go… see below

document.querySelector('[data-page="t0017_add"][data-itemid="integrated_edit_field4"] > * > * > input').value = "Password123";

And here are some notes on finding CSS Selectors by ChatGPT4 I am still investigating CSS Selectors there seems to be a black art to understanding their structure and how they can be useful

I also asked chatGPT about child selectors in CSS. In my discussions with chatGPT I have discovered that spaces are important and symbols are important in naming and of course Javascript is case sensitive. The dynamic nature of CSS Selectors and their very specific naming conventions combined with spaces potentially being characters really means you need to be on your toes when you use them.

PHP Runner : Tips and Tricks – Reload a page from event that belongs to a button

For example on a page we have a button that parses text coming in.

1. Add the following code to Javascript OnLoad event of the table in question

window.tablePage = pageObj

2. Add the following code to any Javascript event where you want to reload the table. Can be ClientAfterevent of any button

if( window.tablePage ) {
window.tablePage.reload({a:'reload'});
}

As far as I can tell what you name the pageObj in this case tablePage is up to you – at the point of naming you are creating this variable.
My hunch is that you would want to name this something relating to the page and something not overly complicated but unique.

The ClientAfterevent references the same pageObj for page refresh.

My understanding of the way the code works is.
On loading the page create a pageObj variable named window.tablePage

After pressing the button if there is a pageObj variable named window.tablePage refresh it!

Javascript – Nubuilder Specific to save Date to DateUpdated field on Record Change (not subform)

From the admin screen go to Develop / Forms / Form of choice / Custom Code / Javascript

To place focus on the search button

function nuLoadBrowse(){
    $('#nuSearchButton').focus();
}

Function to Get System Date

function GetTodayDate() {
    var tdate = new Date();
    var dd = tdate.getDate(); //yields day
    var MM = tdate.getMonth(); //yields month
    var twoDigitMonth = ((tdate.getMonth().length+1) === 1)? (tdate.getMonth()+1) : '' + (tdate.getMonth()+1);
    var yyyy = tdate.getFullYear(); //yields year
    var currdate = dd + "-" + twoDigitMonth + "-" + yyyy;

    return currdate;
}

Function to update field called “DateUpdated” on edit- to be used with the GetTodayDate() function

function nuOnSave() {
if (nuFORM.edited == true)
    {
        $( "#DateUpdated" ).val( GetTodayDate() );
    }
    return true; 

}