Mimic Numbertodate function in SQL VIEW 1

[JaniceA]

I am looking to create some comparative reports based on date but the SQL fields are not date formats. In Crystal I use the Numbertodate formula. How can I accomplish this in an SQL View, Access or Event Manager? In Access I would use the datepart to pull comparatives by quarter, year, month, etc.

 

[dgillz]

Event Manager is already set up to use dates in a YYYYMMDD format, so you do not need to do anything there.

I have this SQL code somewhere, when I have time I will find it and post it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[jmccall]

I've built a cross reference table in SQL (MacolaDates) with two fields, a date field with a SQL field type of smalldatetime and a MacolaDate field int type. I've populated the table with about 10 years of dates aprox 4000 records. I use it and link to it and in SQL views and any time I need reports using any kind of date arithmatic.

 

[spucelik]

Try this...I use it all the time.

SELECT CONVERT(SMALLDATETIME, '20080318' )

Supliment the macola date with a field name or your own date.

Hope it helps!

 

[dgillz]

Sprucelik's example converts TEXT to a date, not a number to a date. So a full example of this would be:

select convert(smalldatetime,convert(varchar(20),inv_dt)) from oehdrhst_sql as mydate
where inv_dt >0

This creates a date time field in the format of 2008-03-18 16:22:45

The where clause is needed to handle zeros or nulls.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[NEmacGuy]

I forgot what app adds the function, perhaps EBA, but I have several sites that there was a SQL Function to that converts the YYYYMMDD number into a SQL DateTime. Just checked, not EBA, but definately in some Exact App, but you could use some of the above code, put it into a Function, and the would call the Function(FieldName) and same some time coding your query.