pstre
asked on
SQL statement - exporting to Excel
I ran a SQL query and copied to Excel. When I did this, I lost the leading zeros on 3 columns --Dept, Division, and ClinProv. What can I change in this query to get the leading zeros back?
[Journal Entry], RTRIM(CAST(DATEPART(mm, T1.[TRX Date]) AS varchar(2))) + '/' + RTRIM(CAST(DATEPART(dd, T1.[TRX Date]) AS varchar(2)))
+ '/' + RTRIM(CAST(DATEPART(yy, T1.[TRX Date]) AS varchar(4))) AS [TRX Date], T1.[TRX Date Month], T1.[TRX Date Year], T1.[Account Number],
T1.[Account Description], T1.[Credit Amount], T1.[Debit Amount], T1.[Net Amount], T1.[Account Description from Account Master],
'' + SUBSTRING(T1.Segment1, 1, 3) AS Dept, T1.[Segment1 Description], '' + SUBSTRING(T1.Segment2, 1, 6) AS Division,
T1.[Segment2 Description], T1.Segment3 AS Clin_Prov, T1.[Segment3 Description], T1.Segment4, T1.[Segment4 Description], T1.Period, ISNULL(T2.USRDEFS2, '')
AS [User Defined 4], T1.Description, T1.[Document Status], T1.[Source Document], T1.[User Who Posted]
FROM dbo.AccountTransactionsEX AS T1 LEFT OUTER JOIN
dbo.GL00100 AS T2 WITH (nolock) ON T2.ACTINDX = T1.[Account Index]
WHERE (T1.[Document Status] = 'open') AND (T1.[TRX Date] BETWEEN '12/01/2013' AND '12/31/2013') AND (T1.Segment4 BETWEEN '100000' AND
'999999')
[Journal Entry], RTRIM(CAST(DATEPART(mm, T1.[TRX Date]) AS varchar(2))) + '/' + RTRIM(CAST(DATEPART(dd, T1.[TRX Date]) AS varchar(2)))
+ '/' + RTRIM(CAST(DATEPART(yy, T1.[TRX Date]) AS varchar(4))) AS [TRX Date], T1.[TRX Date Month], T1.[TRX Date Year], T1.[Account Number],
T1.[Account Description], T1.[Credit Amount], T1.[Debit Amount], T1.[Net Amount], T1.[Account Description from Account Master],
'' + SUBSTRING(T1.Segment1, 1, 3) AS Dept, T1.[Segment1 Description], '' + SUBSTRING(T1.Segment2, 1, 6) AS Division,
T1.[Segment2 Description], T1.Segment3 AS Clin_Prov, T1.[Segment3 Description], T1.Segment4, T1.[Segment4 Description], T1.Period, ISNULL(T2.USRDEFS2, '')
AS [User Defined 4], T1.Description, T1.[Document Status], T1.[Source Document], T1.[User Who Posted]
FROM dbo.AccountTransactionsEX AS T1 LEFT OUTER JOIN
dbo.GL00100 AS T2 WITH (nolock) ON T2.ACTINDX = T1.[Account Index]
WHERE (T1.[Document Status] = 'open') AND (T1.[TRX Date] BETWEEN '12/01/2013' AND '12/31/2013') AND (T1.Segment4 BETWEEN '100000' AND
'999999')
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER