Orion Report Writer question - Date conversion question

Question

I have inherited a system where someone wrote a SQL report in Orion Report Writer that selects the records based on the datetime function.  I dont know much about sql.  Is there a website that someone can post a link to that will let me put in a date (i.e.12/1/2011) and get a number back that represents that date in SQLTIME format ?

Nothing was documented prior to my starting, so I need to get this done and maybe change the report to pulling info another way at a later time when I understand more about orion/npm/sql.

thanks,

Ron

FormerMember · Answer

I am using advance SQL and i copy paste code from existing report for last month and using date date and floating digits. is SQL automatically figure out my last month date and time? how do i write syntax to generate last month report?

tomas.mrkvicka · Answer

If you want to know meaning of these values you can use following queries:

SELECT CAST(40848 AS DATETIME)

SELECT CAST(40876 AS DATETIME)

I really don't know why somebody wrote these queries this way. I would say you can switch these constants with human-readable strings, that means something like:

WHERE (DateTime BETWEEN '2012-01-21' AND '2012-02-21')

There is only one danger - based on your local setting you can have swapped months and days in datetime. Maybe this is reason why somebody wrote it as constants. Probably safest way is to write it in ISO 8601 format - for example '20010225' - format 'YYYYMMDD.'

RonaldNutter · Answer

Here is the part of the SQL from Report Writer that I am working with -

WHERE 
( DateTime BETWEEN 40848 AND 40876 )
 AND  
(

I was hoping for a website that I could go to for converation of a date (i.e. 12/1/2011) to the SQL datetime string (i.e. 40848).

Ron