Link to home
Start Free TrialLog in
Avatar of mjw110
mjw110

asked on

SQL Convert UK to US date format

Please help.  I have the below query and I want to get the dates to use US format MM/DD/YYYY to run a report using Crystal Reports.  I am using the following line of sql:-

      where CONVERT(DATETIME,(substring(A_AUMDTETME, 7, 2) + '/' + substring(A_AUMDTETME, 5, 2) + '/' + left(A_AUMDTETME, 4)),103) BETWEEN {?1StartDate} AND {?EndDate}


The entire sql query is listed below:-

DECLARE @today VARCHAR(50)
DECLARE @previousDate VARCHAR(50), @todayDate VARCHAR(17)

SET @today = (SELECT DATENAME(WEEKDAY, GETUTCDATE()))
SET @todayDate = dbo.CTUTCDate()
SET @previousDate =
CASE
      WHEN @today = 'Monday' THEN
            (SELECT CONVERT(VARCHAR, DATEADD(DAY, -3, GETUTCDATE()), 103))
      ELSE
            (SELECT CONVERT(VARCHAR, DATEADD(DAY, -1, GETUTCDATE()), 103))
END

SELECT
      Table1.[Instrument],
      Table1.[YtD P&L Value]
FROM
(
      SELECT TOP 200
            ISNULL(I.A_TKRCDE, I.A_INSNAM) AS 'Instrument',
            ROUND(SUM(A_ACCDAYPNLVAL), 2) AS 'YtD P&L Value'
      FROM T_ACC_AUMDET AD
      LEFT JOIN T_ATH_INS I ON AD.A_INSID = I.A_INSID
      LEFT JOIN T_ATH_INSTPEMAP ITM ON I.A_INSCLSCDE1 = ITM.A_INSCLSCDE
      WHERE A_AUMID IN
      (
            SELECT A_AUMID
            FROM T_ACC_AUM
      where CONVERT(DATETIME,(substring(A_AUMDTETME, 7, 2) + '/' + substring(A_AUMDTETME, 5, 2) + '/' + left(A_AUMDTETME, 4)),103) BETWEEN {?1StartDate} AND {?EndDate}
            AND A_ACCID = '{?AccountName}'
      )
      --AND ITM.A_INSTPECDE <> 4
      GROUP BY I.A_TKRCDE, I.A_INSNAM
      HAVING ROUND(SUM(A_ACCDAYPNLVAL), 2) <> 0
      --AND ROUND(SUM(A_ACCDAYPNLVAL), 2) < -500000
      --ORDER BY 'YtD P&L Value'
) AS Table1

UNION

SELECT
      Table2.[Instrument],
      Table2.[YtD P&L Value]
FROM
(
      SELECT TOP 200
            ISNULL(I.A_TKRCDE, I.A_INSNAM) AS 'Instrument',
            ROUND(SUM(A_ACCDAYPNLVAL), 2) AS 'YtD P&L Value'
      FROM T_ACC_AUMDET AD
      LEFT JOIN T_ATH_INS I ON AD.A_INSID = I.A_INSID
      LEFT JOIN T_ATH_INSTPEMAP ITM ON I.A_INSCLSCDE1 = ITM.A_INSCLSCDE
      WHERE A_AUMID IN
      (
            SELECT A_AUMID
            FROM T_ACC_AUM
                  where CONVERT(DATETIME,(substring(A_AUMDTETME, 7, 2) + '/' + substring(A_AUMDTETME, 5, 2) + '/' + left(A_AUMDTETME, 4)),103) BETWEEN {?1StartDate} AND {?EndDate}
            AND A_ACCID = '{?AccountName}'
            )
      --AND ITM.A_INSTPECDE <> 4
      GROUP BY I.A_TKRCDE, I.A_INSNAM
      HAVING ROUND(SUM(A_ACCDAYPNLVAL), 2) <> 0
      --AND ROUND(SUM(A_ACCDAYPNLVAL), 2) > 500000
      --ORDER BY 'YtD P&L Value'
) AS Table2
ORDER BY [YtD P&L Value] DESC
Avatar of Mike McCracken
Mike McCracken

What issue are you having?

I believe to convert to a DateTime you need to include a Time in the string.

Is A_AUMDTETME a string in yyyymmdd format?

mlmcc
Avatar of mjw110

ASKER

Hi mlmcc,

The format of A_AUMDTETME is  YYYYDDMM but also contains hours minutes seconds and miliseconds an example is below which would be a date of 2014-08-28  and a time of 1702  55 seconds  607 miliseconds

20140828170255607
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial