Invoice_Dates

Question

Post reply

Invoice_Dates

3935

Mr or Mrs. 500

Points: 511
More actions
June 28, 2012 at 6:16 am

#258211

Hi guys this is my query i wrote to get date in invoice . But i am getting today date . I need date from 06/18/2012 to 06/24/2012

Update #3935

set Invoicenumber = RIGHT ('0000'+Store#,4)+ RIGHT('0'+ CAST(DATEPART(DAY,GETDATE()) AS varchar),2)

+ RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)

+ RIGHT(DATEPART(YEAR,GETDATE()),1)

because the formate of the invoice will be like this (ssssddmmy) First 4 digit is store number .

Does is make bence ?

I am getting result for current date

30328062

35828062

41928062

42528062

54728062

2) Issue

Some of my table store number are 3 digit and some of them are 4 digit . How can i maintain 4 digit?

Because in my sample resultset first 3 digit are store number but i need something like

030328062

035828062

041928062

042528062

054728062

Any idea guys ?

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

you should have continued your question in the original thread.

this logic :

SELECT RIGHT ('0000'+Store#,4)

appends 4 preceeding zeros to the store number, then grabs teh right 4 characters...so store '1' becomes store '0001', like you requested in the original thread.

based on the example where you said you are not getting the preceeding zero, you must be using diffeernt code than what is shown here.

here's an example of what would be generated:



/*--results

000128062

004228062

002228062

039528062

*/

create table #Stores(name varchar(30),Store# varchar(4))

insert into #Stores

SELECT 'Store 1', '1'       UNION ALL

SELECT 'Store 42','42'      UNION ALL

SELECT 'Store 451','22'     UNION ALL

SELECT 'Store 0395','0395'  

SELECT  RIGHT ('0000'+Store#,4)+ RIGHT('0'+ CAST(DATEPART(DAY,GETDATE()) AS varchar),2)

+ RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)

+ RIGHT(DATEPART(YEAR,GETDATE()),1)

FROM #Stores

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

3935 Mr or Mrs. 500 Points: 511 More actions · Answer 2

3935

Mr or Mrs. 500

Points: 511

June 28, 2012 at 6:34 am

#1506556

I used the same one and also in DD part i am getting current date , but i need date from 06/18/2012 to 06/24/2012

How can i do that ?

Lowell SSC Guru Points: 323482 More actions · Answer 3

you'll need to show the exact code you are using.

with that, we can show you how to join against a calendar table to get a range of dates.

based on my own sample data, here's an expansion for a three day example:



/*

--Results

000114062

004214062

002214062

039514062

000115062

004215062

002215062

039515062

000116062

004216062

002216062

039516062

*/

create table #Stores(name varchar(30),Store# varchar(4))

insert into #Stores

SELECT 'Store 1', '1'       UNION ALL

SELECT 'Store 42','42'      UNION ALL

SELECT 'Store 451','22'     UNION ALL

SELECT 'Store 0395','0395'

create table #SomeDates(TheDate datetime)

INSERT INTO #SomeDates

SELECT '20120614' UNION ALL

SELECT '20120615' UNION ALL

SELECT '20120616' 

SELECT  RIGHT ('0000'+Store#,4)+ RIGHT('0'+ CAST(DATEPART(DAY,#SomeDates.TheDate) AS varchar),2)

+ RIGHT('0'+CAST(DATEPART(MONTH,GETDATE()) AS varchar),2)

+ RIGHT(DATEPART(YEAR,GETDATE()),1)

FROM #Stores

CROSS JOIN #SomeDates

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ant-Green SSC Guru Points: 112965 More actions · Answer 4

Ant-Green

SSC Guru

Points: 112965

June 29, 2012 at 2:38 am

#1506989

I have a feeling the store number is an INT and therefore not appending the 0000 string to the front of it, so convert the store number to a char column.

But yes as Lowell detailed this should of gone on the same topic earlier.