Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LisaRoby
Frequent Visitor

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character

The following is my query, I am trying to convert from Oracle to SQL and I have hit a roadblock with this one. The line this is referring to is a datetime2 data type so I am unclear why there is an issue. Any help will be appreciated, sql below:

 

select j."LODDTE" as "Date Loaded",
j."Part Cat" as "Part Cat1",
sum(j."shpqty") as "Loaded Qty", j."WH ID"
from (SELECT ord.ordnum,
ord_line.ordlin,
ord_line.vc_var_fld8,
shipment.alcdte as Allocated,
ord_line.vc_var_fld18,
ord_line.prtnum,
substring(prtmst.prtfam, 1, 2) as "Part_Family",
CASE prtmst.prtfam
WHEN 'S1' THEN 'SRT'
WHEN 'S2' THEN 'SRT'
WHEN 'AA' THEN 'CML ACC'
WHEN 'CC' THEN 'CML ACC'
WHEN 'RA' THEN 'CML ACC'
WHEN 'SA' THEN 'CML ACC'
WHEN 'NS' THEN 'CML ACC'
WHEN 'AY' THEN 'CML ACC'
WHEN 'SP' THEN 'SPP'
WHEN 'SP3' THEN 'SPP'
WHEN 'SPB' THEN 'SPP'
WHEN 'SPMTO' THEN 'SPP'
WHEN 'M1' THEN 'MRT'
WHEN 'IS' THEN 'MRT'
WHEN 'HT' THEN 'RES ACCY'
WHEN 'PA' THEN 'RES ACCY'
WHEN 'AP' THEN 'RES ACCY'
WHEN 'CA' THEN 'RES ACCY'
WHEN 'SS' THEN 'DLS'
WHEN 'SM' THEN 'DLS'
WHEN 'FS' THEN 'DLS'
WHEN 'FM' THEN 'DLS'
WHEN 'AQ' THEN 'Acc'
WHEN 'CA' THEN 'Acc'
WHEN 'DH' THEN 'Acc'
WHEN 'DS' THEN 'Acc'
WHEN 'GY' THEN 'Acc'
WHEN 'HM' THEN 'Acc'
WHEN 'ME' THEN 'Acc'
WHEN 'MF' THEN 'Acc'
WHEN 'UV' THEN 'Acc'
WHEN 'VN' THEN 'Acc'
WHEN 'SC' THEN 'Acc'
WHEN 'FV' THEN 'VRF'
WHEN 'SV' THEN 'VRF'
WHEN 'FC' THEN 'CML FAN COIL'
WHEN 'T1' THEN 'CML SPLIT'
WHEN 'TS' THEN 'ECO ACC'
WHEN 'FN' THEN 'RES COIL'
WHEN 'FR' THEN 'FAN COIL'
WHEN 'SR' THEN 'RES SPLIT'
WHEN 'DA' THEN 'DLS ACC'
WHEN 'VR' THEN 'VRF ACC'
WHEN 'LT' THEN 'LOGO'
WHEN 'GH' THEN 'Geo'
END AS "Part Cat",
ord_line.ordqty,
sum(shipment_line.shpqty) as "shpqty",
min(ord_line.vc_var_fld12) as "Division",
ord.adddte as "Ord Date",
min(ord_line.early_shpdte) as "CRSD",
min(ord_line.late_shpdte) as "TCPSD",
min(ord_line.late_dlvdte) as "CPSD",
ord.vc_var_fld2,
ord.vc_var_fld5,
shipment.ship_id,
shipment.super_ship_id,
shipment.adddte as "Date Added",
car_move.car_move_id,
shipment.track_num,
stop.stop_nam,
Substring(car_move.vc_var_fld3, 5, 2) + '-' + Substring(car_move.vc_var_fld3, 7, 2) + '-' + Substring(car_move.vc_var_fld3, 1, 4) PickUp_date,
Substring(car_move.vc_var_fld1, 5, 2) + '-' + Substring(car_move.vc_var_fld1, 7, 2) + '-' + Substring(car_move.vc_var_fld1, 1, 4) Finalize_Date,
shipment.shpsts,
shipment.srvlvl as "service level",
shipment.carcod,
shipment.VC_SHIPMENT_VFLD3,
trlr.trlr_typ,
trlr.trlr_num,
shipment.stgdte,
shipment.loddte AS "LODDTE",
trlr.close_dte,
trlr.dispatch_dte AS "Dispatch Date",
a2.adrnam Bill_To_Name,
a2.CONT_NAME Sold_To_Number,
ord.vc_var_fld4 CSR_Name,
ord_line.vc_var_fld1,
adrmst.adrnam,
adrmst.adrln1,
adrmst.adrcty,
adrmst.adrstc,
adrmst.adrpsz,
adrmst.ctry_name,
ord.wh_id as "WH ID"
FROM R09CABPRD.ord,
R09CABPRD.ord_line,
R09CABPRD.prtmst,
R09CABPRD.adrmst,
R09CABPRD.adrmst a2,
R09CABPRD.shipment_line,
R09CABPRD.shipment,
R09CABPRD.stop,
R09CABPRD.car_move,
R09CABPRD.trlr
WHERE ord.wh_id = '4300'
and ord.wh_id = ord_line.wh_id
AND ord.client_id = ord_line.client_id
AND ord_line.prtnum = prtmst.prtnum
AND ord.ordnum = ord_line.ordnum
AND ord.rt_adr_id = adrmst.adr_id
AND ord.bt_adr_id = a2.adr_id
AND ord_line.wh_id = shipment_line.wh_id
AND ord_line.client_id = shipment_line.client_id
AND ord_line.ordnum = shipment_line.ordnum
AND ord_line.ordlin = shipment_line.ordlin
AND ord_line.ordsln = shipment_line.ordsln
AND shipment_line.wh_id = shipment.wh_id
AND shipment_line.ship_id = shipment.ship_id
AND shipment.stop_id = stop.stop_id
AND stop.car_move_id = car_move.car_move_id
AND car_move.trlr_id = trlr.trlr_id
and prtmst.prtfam not in ('AA', 'CC', 'RA', 'SA', 'AY', 'HT', 'PA', 'AP', 'DA', 'VR', 'LT', 'TS', 'AP', 'PA', 'AQ', 'CA', 'DH', 'DS', 'GY', 'HM', 'ME', 'MF', 'UV', 'VN', 'SC')
AND adrmst.adrcty not in ('DO NOT SHIP', 'BILLING ONLY', 'BILLING PURPOSE ONLY')
AND adrmst.adrnam not in ('BILLING ONLY DO NOT SHIP', 'Billing only', '**Billing Only DT***', '**BILLING ONLY**DO NOT SHIP**', 'BILLING ONLY - DO NOT SHIP', 'Billing Only - Do not ship', 'DO NOT SHIP - CLAIM 02421929 OVR', 'DO NOT SHIP CLAIM_02442085_OVR', 'billing only do not ship', 'Billing Only')
AND (trlr.dispatch_dte > convert(datetime, '7/29/2020 00:00:00', 126))
GROUP BY ord.ordnum,
ord.adddte,
ord.vc_var_fld2,
ord.vc_var_fld5,
shipment.ship_id,
shipment.super_ship_id,
shipment.track_num,
car_move.car_move_id,
stop.stop_nam,
car_move.vc_var_fld3,
car_move.vc_var_fld1,
shipment.adddte,
shipment.loddte,
trlr.close_dte,
shipment.stgdte,
shipment.carcod,
shipment.shpsts,
shipment.srvlvl,
ord_line.ordlin,
ord_line.vc_var_fld8,
shipment.alcdte,
ord_line.vc_var_fld18,
ord_line.prtnum,
substring(prtmst.prtfam, 1, 2),
prtmst.prtfam,
ord_line.ordqty,
trlr.trlr_typ,
trlr.trlr_num,
trlr.dispatch_dte,
a2.adrnam,
a2.CONT_NAME,
ord.vc_var_fld4,
ord_line.vc_var_fld1,
adrmst.adrnam,
adrmst.adrln1,
adrmst.adrcty,
adrmst.adrstc,
adrmst.adrpsz,
adrmst.ctry_name,
ord.rush_flg,
stop.track_num,
shipment.VC_SHIPMENT_VFLD3,
ord.wh_id) j
group by j."LODDTE",
j."Part Cat",
j."WH ID"
order by j."LODDTE" asc

 

TIA Lisa

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @LisaRoby ,

Change    (trlr.dispatch_dte > convert(datetime, '7/29/2020 00:00:00', 126))  to the below:

trlr.dispatch_dte>=cast('2020-07-29' as date)

1. SELECT * FROM [master].[dbo].[trlr];

vluwangmsft_0-1629441188531.png

2. SELECT * FROM [master].[dbo].[trlr] where (trlr.dispatch_dte > convert(datetime, '7/29/2020 00:00:00', 126));

vluwangmsft_1-1629441218287.png

the same error as your's.

 

3. SELECT *FROM [master].[dbo].[trlr] where trlr.dispatch_dte>=cast('2020-07-29' as date);

vluwangmsft_2-1629441257939.png

successful.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

 

Best Regards

Lucien

 

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @LisaRoby ,

Change    (trlr.dispatch_dte > convert(datetime, '7/29/2020 00:00:00', 126))  to the below:

trlr.dispatch_dte>=cast('2020-07-29' as date)

1. SELECT * FROM [master].[dbo].[trlr];

vluwangmsft_0-1629441188531.png

2. SELECT * FROM [master].[dbo].[trlr] where (trlr.dispatch_dte > convert(datetime, '7/29/2020 00:00:00', 126));

vluwangmsft_1-1629441218287.png

the same error as your's.

 

3. SELECT *FROM [master].[dbo].[trlr] where trlr.dispatch_dte>=cast('2020-07-29' as date);

vluwangmsft_2-1629441257939.png

successful.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

 

Best Regards

Lucien

 

Thank you for your response, however, I took that date field out of the query and I still got that error. The strange part about it is I had another sql similar to this one and the others I was trying to convert, has exactly the same date fields ets. and it ran, so I am using it. I appreciate the time you took to help.

 

Lisa

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.