Need Help with TRIM

Question

Need Help with TRIM

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
June 9, 2009 at 3:20 am

#134853

Hi there - Please find attached part of my SELECT statement -
Select '1' AS Trigger_Status,
dbo.ServiceOrders.UID AS ServiceOrderID,
(SELECT MAX(ServiceOrdersJobs.ScheduledDate+2)
FROM dbo.ServiceOrdersJobs
WHERE dbo.ServiceOrders.UID = dbo.ServiceOrdersJobs.ServiceOrderID
AND (RecordStatusID < 6)) AS PractCompleteDate,
LEFT (dbo.Products.ProductCode, 6) AS SOR,
dbo.SalesOrders.CustomerOrderRef AS ClientRef,
'0' AS SORSequence,
DataWarehouse.dbo.SHG_SORITEMEXPORT.KeySORNumber,
dbo.SalesOrdersItems.Quantity AS SORQty
Once I Select the following data I am outputting to a text file via a DTS script (we are using SQL 2000)
The issue I am having is when exporting the ServiceOrdersJobs.ScheduledDate+2 I am getting the following formatting issue -
The pipe seperated text file looks as follows -
2009-06-06 00:00:00|432605 |MHS00003488000Y|0|2/1471|4
Is there any way that I can just pull back the data - 2009-06-06 instead of haveing the trailing zeros for the time?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

After taking the max, cast it to a string of length 10. Use CONVERT and make sure you get the right format code. (See Books online)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Sorry, how would this code actually be written.

I am a complete novice when it comes to T-SQL, as this is not my code.

Many Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Firstly look up CONVERT in SQL's Books Online. Check the format codes, see which one will give you the date in the format that you want.

Then, add the convert function into the query. The date is returned by the SELECT MAX(...) subquery. You need to apply the CONVERT to the results of that, kinda like this:

(piece of query)

... (SELECT CONVERT(VARCHAR(10), MAX(ServiceOrdersJobs.ScheduledDate+2), <Format code >)

FROM dbo.ServiceOrdersJobs ....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

TSQL Tryer

SSCrazy Eights

Points: 8869

June 9, 2009 at 9:45 am

#1006811

Thanks very much Gail.

I will give it a go

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 5

Still not working, now getting a Line 3: Incorrect syntax near '<'. error when I try and parse the query.

The code now looks like

(SELECT CONVERT(VARCHAR(10), MAX(ServiceOrdersJobs.ScheduledDate+2),)
FROM dbo.ServiceOrdersJobs
WHERE dbo.ServiceOrders.UID = dbo.ServiceOrdersJobs.ServiceOrderID

The 103 is in order to pull the date back as Uk.

Only have to wait a month for my T-SQL course.

Thanks again

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Drop the angle brackets. I meant replace <Format code> with the format code. Angle brackets designates a placeholder. something to be replaced with an actual value.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

Ryan, Gail added the carats to show you where to put the value, change to just 103 and it should work better.

Edit: apologies, Gail, forgot to refresh once I got to this topic and thought I was posting something helpful, not redundant. 😀

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 8

Oh dear 🙂

Thanks guys. This will hopefully make a lot more sense to me next month.

Your help is really appreciated.

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 9

TSQL Tryer

SSCrazy Eights

Points: 8869

June 11, 2009 at 3:55 am

#1007866

Just to let you know I got this working.

Thanks