June 28, 2012 at 6:16 am
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 ?
June 28, 2012 at 6:23 am
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
June 28, 2012 at 6:34 am
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 ?
June 28, 2012 at 7:06 am
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
June 29, 2012 at 2:38 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply