October 4, 2010 at 7:39 am
I have 3 fields in my invoice table YEAR, MONTH, DAY i want to have them actually display as a date
how do i do that
then i would like to use them in a parameter
how do i do that?
SELECT
dbo.INVHDRPF.INBILL,
CASE WHEN dbo.INVHDRPF.INBILL IN ('48702','48700','48701','48709') THEN 'JCP SALESMAN 22'
WHEN dbo.INVHDRPF.INBILL IN ('60400','60401','76595') THEN 'FEDERATED'
WHEN dbo.INVHDRPF.INBILL = '69722' THEN 'SSI 69722'
WHEN dbo.INVHDRPF.INBILL ='69723' THEN 'PEEBLES 69723'
WHEN dbo.INVHDRPF.INBILL = '40260' THEN 'BON TON 40260'
WHEN dbo.INVHDRPF.INBILL ='08540' THEN 'BELK 08540'
WHEN dbo.INVHDRPF.INBILL ='39220' THEN 'GOODYS 39220'
WHEN dbo.INVHDRPF.INBILL ='11465' THEN 'BOSCOVS 11465'
WHEN dbo.INVHDRPF.INBILL IN ('69723','69722') THEN 'SSR/SSI PEEBLES' END CUSTOMER,
dbo.INVDTLPF.INSEAD,
dbo.INVDTLPF.INSTYL,
dbo.COLOR.sycolr,
dbo.COLOR.syclnm,
dbo.INVDTLPF.INCLAS,
case when dbo.INVDTLPF.INCLAS ='P' then 'MISSY'
WHEN dbo.INVDTLPF.INCLAS ='K' then 'PETITE'
WHEN dbo.INVDTLPF.INCLAS ='J' then 'WOMEN'
WHEN dbo.INVDTLPF.INCLAS ='W' THEN 'WOMEN_PETITE' END DIVISION,
CASE WHEN dbo.INVDTLPF.INCLAS = 'J' THEN (SUM(dbo.INVDTLPF.INTOTQ)) END WOMENS_UNITS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'K' THEN (SUM(dbo.INVDTLPF.INTOTQ)) END PETITES_UNITS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'P' THEN (SUM(dbo.INVDTLPF.INTOTQ)) END MISSES_UNITS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'W' THEN (SUM(dbo.INVDTLPF.INTOTQ)) END W_P_UNITS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'J' THEN (SUM(dbo.INVDTLPF.INTOTD)) END WOMENS_DOLLARS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'K' THEN (SUM(dbo.INVDTLPF.INTOTD)) END PETITES_DOLLARS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'P' THEN (SUM(dbo.INVDTLPF.INTOTD)) END MISSES_DOLLARS,
CASE WHEN dbo.INVDTLPF.INCLAS = 'W' THEN (SUM(dbo.INVDTLPF.INTOTD)) END W_P_DOLLARS,
CASE WHEN not(dbo.INVDTLPF.INSEAD in ('CX','F0','S0','XC','XR','XX','XB','S9','F9')) THEN 'FASHION'
WHEN dbo.INVDTLPF.INSEAD='XX' THEN 'XX'
WHEN dbo.INVDTLPF.INSEAD='XB' THEN 'XB'
WHEN dbo.INVDTLPF.INSEAD='S9' THEN 'S9/F9'
WHEN dbo.INVDTLPF.INSEAD='F9' THEN 'S9/F9'
ELSE dbo.INVDTLPF.INSEAD END SEASON,
dbo.INVHDRPF.ININVY,
dbo.INVHDRPF.ININVM,
dbo.INVHDRPF.ININVD
FROM
dbo.INVHDRPF
INNER JOIN
dbo.INVDTLPF ON dbo.INVHDRPF.INICNA = dbo.INVDTLPF.INICND
AND dbo.INVHDRPF.ININV# = dbo.INVDTLPF.ININV#
INNER JOIN
dbo.COLOR ON dbo.INVDTLPF.INCLOR = dbo.COLOR.sycolr
WHERE (dbo.INVHDRPF.INBILL IN ('48702', '48700', '48701', '48709', '60400', '60401', '76595', '69722', '69723', '40260','08540', '39220', '11465'))
AND dbo.INVDTLPF.INSTYL <>0
GROUP BY
dbo.INVHDRPF.INBILL,
dbo.INVDTLPF.INSEAD,
dbo.INVDTLPF.INSTYL,
dbo.COLOR.sycolr,
dbo.COLOR.syclnm,
dbo.INVDTLPF.INCLAS,
dbo.INVHDRPF.ININVY,
dbo.INVHDRPF.ININVM,
dbo.INVHDRPF.ININVD
October 4, 2010 at 8:12 am
There may be other ways, but here is one way:
DECLARE @m INT
DECLARE @d INT
DECLARE @y INT
SET @y = 2010
SET @d = 4
SET @m = 10
SELECT CAST(CAST(@y AS VARCHAR(4)) + RIGHT('0' + CAST(@m AS VARCHAR(2)), 2) + RIGHT('0' + CAST(@d AS VARCHAR(2)), 2) AS DATETIME)
This is how to get the values from the table to a datetime.
-- Cory
October 4, 2010 at 8:20 am
Part of this depends on how the values are stored, as INTs or CHARs. Is the year stored as 2 digits or 4?
If stored as INTs, one way is:
cast(( yy * 10000) + (mm * 100) + dd as datetime);
October 4, 2010 at 8:34 am
the values are integers
i tried this
cast(( dbo.INVHDRPF.ININVY * 10000) + (dbo.INVHDRPF.ININVM* 100) + dbo.INVHDRPF.ININVY as datetime)
these were my results
YEAR MONTH DAY RESULT
7 11 12 2094-09-07 00:00:00.000
Did i miss understand what i needed to use in the cast statement?
October 4, 2010 at 8:59 am
Not missing anything other than me leaving something while typing on my blackberry. Looks like your year value is two digits, no leading zero, is this correct?
Back in a bit, have a meeting to go to now.
October 4, 2010 at 9:09 am
sharonmtowler (10/4/2010)
the values are integersi tried this
cast(( dbo.INVHDRPF.ININVY * 10000) + (dbo.INVHDRPF.ININVM* 100) + dbo.INVHDRPF.ININVY as datetime)
these were my results
YEAR MONTH DAY RESULT
7 11 12 2094-09-07 00:00:00.000
Did i miss understand what i needed to use in the cast statement?
Here is a testing version now that I know the year is 2 digits
DECLARE @m INT
DECLARE @d INT
DECLARE @y INT
SET @y = 7
SET @d = 4
SET @m = 10
SELECT CAST(RIGHT('0' + CAST(@y AS VARCHAR(2)), 2) + RIGHT('0' + CAST(@m AS VARCHAR(2)), 2) + RIGHT('0' + CAST(@d AS VARCHAR(2)), 2) AS DATETIME)
-- Cory
October 4, 2010 at 12:48 pm
Here is another method:
dateadd(dd, ININVD - 1,
dateadd(mm, ININVM - 1,
dateadd(yy, ININVY + 100, cast('19000101' as datetime))))
Can't test this from where I am at, so hope it helps.
October 4, 2010 at 1:22 pm
This is probably the fastest code for the conversion (assuming that the year is an integer in the form YYYY)
select dateadd(month,(12*@Year)-22801+@Month,@Day-1)
If the year is two digits, you will have to apply some windowing logic to it first, before using the code above, depending on what is best for your application:
select @year =
case
when year between 0 and 50 then 2000+year
when year between 51 and 99 then 1900+year
else null end
There was extensive testing of different methods on this link:
Make Date function
October 4, 2010 at 1:32 pm
Based on the OP second post, it appears that the year is stored as the last two digits of the year as an integer. Meaning that there is no leading zero.
October 5, 2010 at 6:24 am
thank you very much that worked great.
i used
dateadd(dd, ININVD - 1,
dateadd(mm, ININVM - 1,
dateadd(yy, ININVY + 100, cast('19000101' as datetime))))
October 5, 2010 at 6:41 am
is there a way to make the datetime just a date???
October 5, 2010 at 7:33 am
sharonmtowler (10/5/2010)
is there a way to make the datetime just a date???
Convert it to the DATE data type.
October 5, 2010 at 10:38 am
how do i convert it?
October 5, 2010 at 10:42 am
cast((code from above) as date)
Start with this, if you still have problems let us know.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply