INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

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

Hi,

Quote:

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???

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

(OP)
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}

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

(OP)
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}

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Thanks Skip. Will try this later this afternoon.

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

(OP)
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?

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

(OP)
The statements below also give me an error.

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

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

(OP)
Got the statement below to work. Thanks.

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

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

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close