How to join Excel Spreadsheet table "Date" field to MS SQL Server "Date" field

[capronton]

I need to join the two "times" below. I have already joined the "number" fields
in the Excel Spreadsheet that I imported into the Crystal Report, however I cannot
join the "Date" field. Any idea on how to join Excel Date with MS SQL Server Date?

In the EXcel Table that I import into Cystal Reports the date is formatted like this
12/30/1899 5:00:00AM
(i.e., the actual spreadsheet only contains the time but for some reason when I import
into Crystal Reports it puts "12/30/1899" on the time)

In the MS SQL Server Database table the date is formatted like this
5/27/16 5:00 am

How can I join just the "times" in both the Excel Table and the database table?

In other words 5:00am in Excel joined to 5:00am in the MS SQL Database.

Thanks for any assistance.

 

[SkipVought]

Hi,

In the EXcel Table that I import into Cystal Reports the date is formatted like this
12/30/1899 5:00:00AM

That appears to be a value very close to ZERO, i.e. 1/1/1900. Hmmmmmmmm???

In other words 5:00am in Excel joined to 5:00am in the MS SQL Database.

Why would you be only using the TIME portion of DateTime, as your DATE part could be different?

Where is the SQL code for the join?



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[capronton]

Skip, below is a sample of the Excel Spreadsheet I imported into Cystal Reports.
I need to be able to join the (blk, startTrip,dir2, and tpID) fields to pull in
the schHdwyTrapeze field into report and match the correct schHdwyTrapeze number
with the correct blk,startTrip,dir2, and tpID in the crystal report.

I can join the number fields, but cannot join the "startTrip" time field.
Please keep in mind that "startTrip" has no date associated with it, only the time.
We only want to join "time" to "time". Not sure if this is possible.
Thanks for any assistance.

blk direction startTrip timepoint schHdwyTrapeze dir2 tpID
803002 NB [/indent]5:00:00 WESTGATE 0 [/indent]4 12644
803003 NB [/indent]5:15:00 WESTGATE 15 [/indent]4 12644
803005 NB [/indent]5:30:00 WESTGATE 15 [/indent]4 12644
803007 NB [/indent]5:45:00 WESTGATE 15 [/indent]4 12644
803009 NB [/indent]6:00:00 WESTGATE 15 [/indent]4 12644
803001 NB [/indent]6:15:00 WESTGATE 15 [/indent]4 12644
803030 NB [/indent]6:30:00 WESTGATE 15 [/indent]4 12644

 

[capronton]

Below is the SQL code for the join. Thanks again.

O:\JGov\Ridership\Ridership Summaries\FY2011\MetroRail Analysis\CrystalReportQuerys\UpLoadReports\TrapezeHeadways_ExcelTable\TrapezeHeadway_forHeadway_OTP_v3.xls
SELECT `schdHeadway_Trapeze_`.`schHdwyTrapeze`, `schdHeadway_Trapeze_`.`blk`, `schdHeadway_Trapeze_`.`dir2`, `schdHeadway_Trapeze_`.`tpID`
FROM `schdHeadway_Trapeze$` `schdHeadway_Trapeze_`
EXTERNAL JOIN schdHeadway_Trapeze_.blk={?cmtaocltdb: Command.block_id} AND schdHeadway_Trapeze_.dir2={?cmtaocltdb: Command.direction_code_id} AND schdHeadway_Trapeze_.tpID={?cmtaocltdb: Command.tp_id}


cmtaocltdb
SELECT
day_type_vs.description,incident_log.incident_date_time, incident_log.sched_time, incident_log.transit_date_time, incident_log.block_id, incident_log.current_route_id,incident_log.direction_code_id,
incident_types.incident_name, incident_types.incident_desc, incident_log.route_id, incident_log.vehicle_id, incident_log.driver_id, incident_log.deviation,
direction_codes.direction_description, timepoint_name.tp_lname, incident_log.incident_log_id, timepoints.tp_loc_x, timepoints.tp_loc_y,
timepoints.tp_radius, day_type_vs.description, trip_timepoint.seq_num, garage.garage_description, incident_log.tp_id, trip.end_tpid, trip_timepoint.tp_id,
trip.start_time, trip.end_time, trip.trip_id_external,
trip.trip_id,headway_log.scheduled_headway,headway_log.actual_headway,headway_log.actual_leader
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
LEFT OUTER JOIN headway_log headway_log ON headway_log.headway_log_id=incident_log.incident_log_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version


INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.description=day_type_vs.description) AND (vehicle_schedule.day_type_vs=day_type_vs.id)
WHERE (incident_types.incident_name='BUNCH-E' OR incident_types.incident_name='BUNCH-L' OR incident_types.incident_name='BUNCH-N' OR incident_types.incident_name='EARLY' OR incident_types.incident_name='GAP-E' OR

incident_types.incident_name='GAP-L' OR incident_types.incident_name='GAP-N' OR incident_types.incident_name='LAYOVER' OR incident_types.incident_name='LATE' OR incident_types.incident_name='NORMAL' OR incident_types.incident_name='NORMAL-E' OR

incident_types.incident_name='NORMAL-L' OR incident_types.incident_name='NORMAL-N')
AND (incident_date_time>= {?StartDate} AND incident_log.incident_date_time<={?EndDate})
AND (incident_log.incident_log_id % 2) = 1

ORDER BY incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,trip_timepoint.seq_num,incident_log.tp_id, incident_log.incident_date_time
EXTERNAL JOIN Command.block_id={?O:\JGov\Ridership\Ridership Summaries\FY2011\MetroRail Analysis\CrystalReportQuerys\UpLoadReports\TrapezeHeadways_ExcelTable\TrapezeHeadway_forHeadway_OTP_v3.xls: schdHeadway_Trapeze_.blk} AND Command.direction_code_id={?O:\JGovea\Ridership\Ridership Summaries\FY2011\MetroRail Analysis\CrystalReportQuerys\UpLoadReports\TrapezeHeadways_ExcelTable\TrapezeHeadway_forHeadway_OTP_v3.xls: schdHeadway_Trapeze_.dir2} AND Command.tp_id={?O:\JGovea\Ridership\Ridership Summaries\FY2011\MetroRail Analysis\CrystalReportQuerys\UpLoadReports\TrapezeHeadways_ExcelTable\TrapezeHeadway_forHeadway_OTP_v3.xls: schdHeadway_Trapeze_.tpID}

 

[capronton]

This is the "startTrip" Date join. The report will not run when I add the join below.

EXTERNAL JOIN schdHeadway_Trapeze_.startTrip={?cmtaocltdb: Command.start_time}

 

[SkipVought]

You may need to CONVERT the value that you are entering, to a Timevalue, whatever function is available in your SQL editor, like TimeSerial or ToDate.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[capronton]

Thanks Skip. Will try this later this afternoon.

 

[capronton]

Skip, instead of using Excel to try to pull in "schHdwyTrapeze" then I hard-coded the value in the formula below.
However in the statement below the {Command.start_time} = datetime("7:00AM") is not working.

If {Command.block_id} = 803006 and {Command.direction_code_id} = 4 and {Command.start_time} = datetime("7:00AM") and {command.tp_id} = 12644
then 15 else
If {Command.block_id} = 803002 and {Command.direction_code_id} = 4 and {Command.start_time} = datetime("8:12AM")and {command.tp_id} = 12644
then 11 else 0

I'm trying to set "schHdwyTrapeze" = 15 when {Command.start_time} = datetime("7:00AM")
and set "schHdwyTrapeze" = 11 when {Command.start_time} = datetime("8:12AM")

If start_time is 7am then how can I set {Command.start_time} to 7am?

 

[capronton]

The statements below also give me an error.

{Command.start_time} = timeserial(7,0,0)
{Command.start_time} = timeserial(8,12,0)

 

[capronton]

Got the statement below to work. Thanks.

time({Command.start_time}) = time(7,0,0)

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]