Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Mimic Numbertodate function in SQL VIEW 1

Status
Not open for further replies.

JaniceA

IS-IT--Management
Mar 6, 2008
3
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.
 
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

"What version of URGENT!!! are you using?
 
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.
 
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!
 
Sprucelik's example converts TEXT to a date, not a number to a date. So a full example of this would be:

Code:
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

"What version of URGENT!!! are you using?
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top