Formula help ... With Dates

I am pulling in the 2 digit month 01,02,03 etc.... and the four digit year(2005)
I want a formula to only pull the previous 12 months...
Any help... Ideas? I am fairly new to CR... not good w/ the formulas yet.Create a formula and put your date field in there and subtract 365 (the number of days in a year. Also, there is a Year(X) function, this should also prove to be useful.sql

Formatting syntax

Hi,

I am trying to convert an MS Access query to SQL script.The query builds a 16 digit field by combining 3 different columns.

access query

Select

"SP"& Format([CusNum],"000")& " "& Format$([InVoiceNum],"00000000") & "-" & Format$(,"00") AS RefNo,

From PurchaseOrder

RefNo

SP053 00000001-00

SP053 00000010-00

SP05314556895-00

SQL query to replace access query

Select

'SP'+Isnull(Cast(CusNumasvarchar(3)),'000')+''+Isnull(Cast(InVoiceNumasvarchar(8)),'00000000')+'-'+Isnull(Cast(PIasvarchar(2)),'00')

From dbo.PurchaseOrder

SP0531-0

SP05310-0

SP05314556895-00

I need some help in putting together a query that will give me the same results as produced by the access query.If InvoiceNum is less than 8 digits long I don’t know how to pad the field with 0000 to get it to be desired length.

Thanks for your help.

Nats

Nats:

Maybe something like this:

declare @.aNumber integer set @.aNumber = 715

select @.aNumber as [Input Number],
right ('0000000' + convert(varchar(8), @.aNumber), 8) as [formatted Number]

Input Number formatted Number
-
715 00000715

( Bemoaning my rustiness at Access; I wrote part of Access 97 Unleashed 2nd Ed, but I haven't used Access really since the previous millenium -- SHEESH! )

|||

Try the example below.

Chris

SELECT 'SP'

+ RIGHT('000' + ISNULL(CAST([CusNum] AS VARCHAR(3)), ''), 3)

+ ' '

+ RIGHT('00000000' + ISNULL(CAST([InVoiceNum] AS VARCHAR(8)), ''), 8)

+ '-'

+ RIGHT('00' + ISNULL(CAST(PI AS VARCHAR(2)), ''), 2)

FROM dbo.PurchaseOrder

--Note that PI should be in square brackets, however for some reason when I do this in the forum I end up with a piece of pizza, like so:

|||

Awesome works great...

Thanks a bunch!

formatting ints in table

Hi I am storing a customer code in my db that will always be 9 digits long. Sometimes the first digit is zero which gets knocked off when added in.

e.g code 050101111 is stored as 50101111

Can anyone suggest a way of formatting it so it will always be 9 digits long. A similar thing can be done in Excel, anyone have any ideas?

much appreciated

TomUse char(9) as the data type in SQL Server.|||thanks for your help

Tom