help in date format

EriK Davila 26 Reputation points
2021-03-01T19:41:12.263+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,102 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,581 questions
{count} votes

5 answers

Sort by: Most helpful
  1. EriK Davila 26 Reputation points
    2021-03-01T20:09:22.233+00:00

    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


  2. Erland Sommarskog 103.8K Reputation points MVP
    2021-03-01T22:38:36.05+00:00
    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.


  3. EchoLiu-MSFT 14,576 Reputation points
    2021-03-02T05:47:21.787+00:00

    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

    0 comments No comments

  4. Olaf Helper 42,561 Reputation points
    2021-03-02T06:52:07.147+00:00

    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)  
    
    0 comments No comments

  5. Tom Phillips 17,716 Reputation points
    2021-03-02T18:40:36.827+00:00

    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.

    0 comments No comments