March 31, 2013 at 12:25 pm
I have tried using the TRIM function in the below query to remove a leading zero in the Department Code (vEPayrollBase.[PrbPayDept] AS [Department Code]). Does anyone know how what the correct language is? Also, how would I modify Date of Birth to format as mm/dd/yyyy instead of the default? Thanks
SELECT vEPerson.[EpFirstName] AS [First Name],
vEPerson.[EpMiddleName] AS [Middle Name],
vEPerson.[EpLastName] AS [Last Name],
vEPerson.[EpEmail] AS [E-mail address],
vEJob.[EjTitle] AS [Job Title],
vEbase.[EbClock] AS [Employee ID],
vEPayrollBase.[PrbPayDiv] AS [Location Code],
vEPayrollBase.[PrbPayDept] AS [Department Code],
(CASE
WHEN [EpCountry] = 'USA' THEN 'US'
ELSE [EpCountry]
END) AS [Country],
(CASE
WHEN [EeStatus] = 'Active' THEN 'A'
ELSE [EeStatus]
END) AS [Status Indicator],
vEPerson.[EpDateBorn] AS [Date Of Birth],
vEPerson.[EpSex] AS [Gender],
vEPerson.[EpStreet1] AS [Home address],
vEPerson.[EpStreet2] AS [Home address 2],
vEPerson.[EpCity] AS [City],
vEPerson.[EpState] AS [State],
vEPerson.[EpZip] AS [Zip],
vEPerson.[EpHomePhone] AS [Home Phone],
vEJob.[EjWorkPhone] AS [Work Phone],
vEPerson.[EpCellPhone] AS [Mobile Phone ],
vEPayrollBase.[PrbDateOriginalHire] AS [Original Hire Date]
FROM vEPerson
INNER JOIN vEbase
ON vEbase.[EbFlxID] = vEPerson.[EpFlxIDEb]
INNER JOIN vEJob
ON vEbase.[EbFlxID] = vEJob.[EjFlxIDEb]
INNER JOIN vEPayrollBase
ON vEbase.[EbFlxID] = vEPayrollBase.[PrbFlxIDEb]
INNER JOIN vEEmploy
ON vEbase.[EbFlxID] = vEEmploy.[EeFlxIDEb]
WHERE (((((vEPerson.[EpDateBeg] <= '2020-06-20 23:59:59'
AND (vEPerson.[EpDateEnd] >= '2020-06-20 00:00:00'
OR vEPerson.[EpDateEnd] IS NULL)))
AND (((vEJob.[EjDateBeg] <= '2020-06-20 23:59:59'
AND (vEJob.[EjDateEnd] >= '2020-06-20 00:00:00'
OR vEJob.[EjDateEnd] IS NULL))
AND vEJob.[EjPrimary] = 'P')))
AND (vEbase.[EbArchive] <> 'Y'
AND vEbase.[EbFlagEmp] = 'Y'))
AND (vEPayrollBase.[PrbDateBeg] <= '2020-06-20 23:59:59'
AND (vEPayrollBase.[PrbDateEnd] >= '2020-06-20 00:00:00'
OR vEPayrollBase.[PrbDateEnd] IS NULL)))
AND (vEEmploy.[EeDateBeg] <= '2020-06-20 23:59:59'
AND (vEEmploy.[EeDateEnd] >= '2020-06-20 00:00:00'
OR vEEmploy.[EeDateEnd] IS NULL))
March 31, 2013 at 12:40 pm
I hope the following code snippets help you:
declare @AccountNumber varchar(8) = '0023456';
select
patindex('%[1-9]%', @AccountNumber),
len(@AccountNumber),
len(@AccountNumber) - patindex('%[1-9]%', @AccountNumber) + 1,
right(@AccountNumber, len(@AccountNumber) - patindex('%[1-9]%', @AccountNumber) + 1);
declare @MyDate datetime = getdate();
select @MyDate, convert(varchar(10), @MyDate, 101);
March 31, 2013 at 12:50 pm
I'm having trouble incorporating this into my query though. I get forbidden text messages.
March 31, 2013 at 12:55 pm
teninthreeforms (3/31/2013)
I'm having trouble incorporating this into my query though. I get forbidden text messages.
You need to take what I showed you and use it in your code. Don't use the variables I declared and used, use the appropriate columns in your query where you need the leading zeros removed and the date formatted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy