help in date format

Question

CAST(DATEADD(HOUR,-6,j.JobStarted) AS varchar) AS 'DelWindowStart', how can I format the date for only to have the 02/11/21 Date, month, and year

right now is Feb 1 2021 1:01 PM like this

Answer 1

will this work as this is the actual query

SELECT o.OrderID,
o.ExternalOrderID,
TRIM(ISNULL(o.CustFn, '') + ' ' + ISNULL(o.CustLN, '')) as CustomerName,
p.Name as PartnerName,
CASE
WHEN (o.BrandName IS NOT NULL)
THEN o.BrandName
ELSE s.StoreName
END AS StoreName,
a.AreaName,
o.ZoneName,
o.PickupAddress,
o.MerchantZipCode as 'PickupZip',
o.DropoffAddress,
****CAST(DATEADD(HOUR,-6,j.JobStarted) AS varchar) AS 'DelWindowStart', this are the one that I need to get them correct for me as 02/01/21
CAST(DATEADD(HOUR,-6,j.JobEnded) AS varchar) AS 'DelWindowEnd',**** this are the one that I need to get them correct for me as 02/01/21
CAST(DATEADD(HOUR,-6,o.DriverArriveTime) AS varchar) as 'DriverArriveTime',
DATEDIFF(minute, j.JobStarted, o.DriverArriveTime) as 'ArrivalDrift',
CAST(DATEADD(HOUR,-6,o.DriverOutForDeliveryTime) AS varchar) AS 'PickTime', --CASE WHEN o.DriverOutForDeliveryTime IS Not NULL THEN o.DriverOutForDeliveryTime ELSE jo.PickTime END AS 'PickTime' ,
DATEDIFF(minute, o.DriverArriveTime, o.DriverOutForDeliveryTime) as 'DriverWaitTime',
CAST(DATEADD(HOUR,-6,jo.DropTime) AS varchar) as DropTime,
DATEDIFF(minute, j.JobEnded, jo.DropTime) as 'DeliveryDrift',
j.Status,
ROUND(ISNULL(((SELECT TotalDistance From Jobs Where JobId = j.JobId) / 1609.344),0),2) as 'TotalMiles',
ROUND(ISNULL(j.MileageRate, 0),2) as 'MileageRate',
(ROUND(ISNULL(((SELECT TotalDistance From Jobs Where JobId = j.JobId) / 1609.344),0),2) * ROUND(ISNULL(j.MileageRate, 0),2)) as 'TotalMileageRate',
ROUND(ISNULL(j.BaseRate, 0),2) as 'BaseRate',
ROUND(ISNULL(j.GuaranteeRate, 0),2) as 'GuaranteeRate',
ROUND(ISNULL(j.DeliveryFee, 0),2) as 'DeliveryFee',
ROUND(ISNULL(j.MileageFee, 0),2) as 'MileageFee',
ROUND(ISNULL(j.OtherFee, 0),2) as 'OtherFee',
ROUND(ISNULL(j.ReturnFee, 0),2) as 'ReturnFee',
ROUND(ISNULL(j.PeakHourRate, 0),2) as 'PeakHourRate',
ROUND(ISNULL(j.CancellationFee, 0),2) as 'CancellationFee',
ROUND(ISNULL(j.ExtraRate, 0),2) as 'Tips',
ROUND(ISNULL((select sum(ot.CalculatedTip) from OrderTips as ot where ot.Orderid = o.OrderId), 0.00),2) as 'PostTips',
convert(varchar(50),jo.DelayTime) as DelayTime,
jo.DelayReason,
CASE
WHEN o.IsOrderReturned=1 THEN 'True'
ELSE 'False'
END as IsOrderReturned,
CASE
WHEN o.ActualDeliveryTime IS NULL THEN 'No Info'
WHEN o.ActualDeliveryTime > o.DelWindowEnd THEN 'Late'
WHEN o.ActualDeliveryTime < o.DelWindowStart THEN 'Early'
WHEN o.ActualDeliveryTime BETWEEN o.DelWindowStart AND o.DelWindowEnd THEN 'Ontime'
ELSE 'Other'
END AS OnTimeDeliveryFlag,
o.IsSkipcartCancelled as 'IsAdminCanceled',
o.IsRetailerCancelled as 'IsRetailerCancelled',
o.DescriptionOfRetailerCancel as 'OrderStateAtRetailerCancel',
(SELECT top 1 ExceptionalEvent FROM JobEvents WHERE OrderId = O.OrderID and EventName = 'close_order') as 'ReasonCode',
TRIM(ISNULL(d.FirstName, '') + ' ' + ISNULL(d.LastName, '')) as DriverName,
-- o.NoOfBags,
-- jo.NoOfBags as NoOfBagsPicked,
-- o.OrderStatus
ISNULL(o.BillingDeliveryFee,0) as 'BillingDeliveryFee',
ISNULL(o.BillingReturnFee,0) as 'BillingReturnFee',
ISNULL(o.BillingCancellationFee,0) as 'BillingCancellationFee',
ISNULL(o.BillingMileageFee,0) as 'BillingMileageFee',
ISNULL(o.BillingTip,0) as 'BillingTip'
FROM Orders o WITH (NOLOCK)
LEFT JOIN JobOrders jo WITH (NOLOCK) ON o.OrderID = jo.OrderId and jo.IsArchived = 0
LEFT JOIN Jobs j WITH (NOLOCK) ON jo.JobId = j.JobId
LEFT JOIN Drivers d ON J.DriverId = d.DriverId
LEFT JOIN Store s WITH (NOLOCK) ON s.StoreId = o.StoreID
LEFT JOIN Partner p WITH (NOLOCK) ON p.PartnerId = o.PartnerId
LEFT JOIN Regions r WITH (NOLOCK) ON o.RegionId = r.RegionId
LEFT JOIN OrderArea oa WITH (NOLOCK) ON o.OrderID = oa.OrderId
LEFT JOIN Areas a WITH (NOLOCK) ON oa.AreaId = a.AreaId
WHERE o.DelWindowStart BETWEEN '02/01/2021'and '02/28/2021'
and (j.Status ='cancelled' or j.Status='delivered')
GROUP BY o.OrderID,
o.ExternalOrderID,
o.CustFn,
o.CustLN,
a.AreaName,
o.ZoneName,
o.PickupAddress,
o.MerchantZipCode,
o.DropoffAddress,
j.JobStarted,
j.JobEnded,
s.StoreName,
-- o.NoOfBags,
-- jo.NoOfBags,
--- o.OrderStatus,
jo.DelayReason,
o.IsOrderReturned,
j.Status,
jo.PickTime,
jo.DropTime,

jo.DelayTime, 
j.JobId, 
j.MileageFee, 
j.BaseRate, 
j.GuaranteeRate, 
j.DeliveryFee, 
j.OtherFee, 
j.ReturnFee, 
j.PeakHourRate,
j.CancellationFee, 
j.ExtraRate, 
o.Tip, 
d.FirstName, 
d.LastName, 
j.MileageRate, 
p.Name, 
o.BrandName, 
o.IsSkipcartCancelled, 
o.DriverArriveTime, 
o.DriverOutForDeliveryTime,
o.ActualDeliveryTime, 
o.DelWindowStart, 
o.DelWindowEnd, 
o.IsRetailerCancelled, 
o.DescriptionOfRetailerCancel,
o.ExternalOrderID, 
o.BillingDeliveryFee, 
o.BillingReturnFee, 
o.BillingCancellationFee,
o.BillingMileageFee,
o.BillingTip

ORDER BY p.Name, DelWindowStart

Answer 2

convert(char(8), DATEADD(HOUR,-6,j.JobStarted, 3)

However, I would give this a second thought. The best is to return date and time values to the client, and let the client format according to the user's regional settings.

By the way, don't use NOLOCK if you don't understand exactly what it does. With NOLOCK you may read uncommitted and inconsistent data. You may also fail to read data that has been committed for a long time.

Answer 3

Hi @EriK Davila ，

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

Please provide a minimal example of data.

Echo

Answer 4

Other option is to use the CONVERT function, here you can pass a format parameter, e.g. 101 for US format, see CAST and CONVERT (Transact-SQL) =>
Date and Time styles

select convert(varchar(10), getdate(), 101)  

Answer 5

JobStarted and JobEnded are obviously not datetime data types. I assume they are varchars. Without sample data and the format of the value in the varchar field, it is difficult to help you with your problem.