UCCX 7 Script - get date from database

[AndreasSchaupp]

I need a script to get current date for holiday from database. i tried following...

i set an string value = D[now] as short date.

In the database are the following entries as varchar:

Datum

--------------

11/10/10
11/3/10
11/4/10
11/2/10

To access the database i used the DB Read object with this sql statement

SELECT Datum FROM Holiday WHERE Datum = $todaysDateString

but it did not work! I should get back 1 row.

What is the catch?

Attached two screens for further understanding.

THX!

[Anthony Holloway]

First of all, you shouldn't be using the local instance of SQL server modifying the CRS database, unless you fully understand why you shouldn't, and still choose to do so anyway.

You should be using an XML document from the repository if you want to keep the solution self contained, or an external database if you really want to use a database.

Ok, but to address your immediate issue.  I see you pressed the Test button and are alarmed that the result was 0.  To get you to think about why, I will ask you, when you press Test, what is the value of the variable in the script editor for todaysDateString?  Is it a date, or is it an empty string ("")?

Message was edited by: Anthony Holloway - Edited for clarity (i hope)

[AndreasSchaupp]

Hello Anthony, thanks for your replay!

I use the local instance of sql server for tests in my lab. I do not use it in a productive environment.

I know the solution with the xml file but i want to get the date from the database. So it is possible to add a new date by php script.

The value of the variable todaysDateString is an empty string. to get the date from database i use a string value. because the date in database looks like 11/03/10. Or is this the wrong approach?

[Anthony Holloway]

No it's not wrong, but you have to understand.  If your variable's value is == "", then your SQL statement is looking for records where the date is an empty string.  Try typing a date into the variable before running the test, or replace the variable in the SQL statement with a short date string.

Alternatively, you could skip the Test button, and just run a debug.