TRIM and Date Format question

Question

Post reply

TRIM and Date Format question

teninthreeforms

Grasshopper

Points: 18
More actions
March 31, 2013 at 12:25 pm

#394498

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))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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);

teninthreeforms Grasshopper Points: 18 More actions · Answer 2

I'm having trouble incorporating this into my query though. I get forbidden text messages.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.