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,(substrin g(A_AUMDTE TME, 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,(substrin g(A_AUMDTE TME, 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,(substrin g(A_AUMDTE TME, 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
where CONVERT(DATETIME,(substrin
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),
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,(substrin
AND A_ACCID = '{?AccountName}'
)
--AND ITM.A_INSTPECDE <> 4
GROUP BY I.A_TKRCDE, I.A_INSNAM
HAVING ROUND(SUM(A_ACCDAYPNLVAL),
--AND ROUND(SUM(A_ACCDAYPNLVAL),
--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),
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,(substrin
AND A_ACCID = '{?AccountName}'
)
--AND ITM.A_INSTPECDE <> 4
GROUP BY I.A_TKRCDE, I.A_INSNAM
HAVING ROUND(SUM(A_ACCDAYPNLVAL),
--AND ROUND(SUM(A_ACCDAYPNLVAL),
--ORDER BY 'YtD P&L Value'
) AS Table2
ORDER BY [YtD P&L Value] DESC
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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