var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Identify record in period
I have a transaction table and I have a account table.
In the account table I have a StartDate and EndDate both are DataTypes nVarChar(50)
In the transaction table I have bookings with a EnterDate (nVarChar(50))
I need to identify which records in the Trans table fall within the Start and End Date of the account table.
This I need to show in a SQL View.
can't be done with nvarchar dates
change your tables and convert your dates, and then it will be possible
Two questions now:
1. How to I convert my dates (I have no problem changing the field type as this is a new table)?
2When I have it in date format what would the SQL be to identify those records?
Many many thanks,
sorry, i can't tell you how to convert the dates, beause i can't see what format they're in now
if you can rebuild the tables and reload the data, that would be best
as for the query, just do an INNER JOIN using BETWEEN in the ON clause
If I do that will it not only show the records that fall between these dates. What I want to do is show all records and identify the one that fall within these dates by creating another filed in the view.
I can get this to work if I link MS Access to the tables and write the query there but if I transfer this to SQL Server the SQL does not work.
This is the SQL in MS Access
SELECT IIf(Transactions.ConnectTime<Conf_Dates.StartDate, 'Yes','No') AS StartOK
FROM Transactions LEFT JOIN Conf_Dates ON CDR_Transactions.ConfRefNum = Conf_Dates.ConfRefNum;
remove the IIF function and, instead, use a CASE expression
the syntax is in da manual
Got it and now working good - Thats what I was looking for.