date in 3 seperate fields

  • 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

  • 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

  • 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);

  • 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

    711122094-09-07 00:00:00.000

    Did i miss understand what i needed to use in the cast statement?

  • 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.

  • sharonmtowler (10/4/2010)


    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

    711122094-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

  • 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.

  • 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

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

  • 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.

  • 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))))

  • is there a way to make the datetime just a date???

  • sharonmtowler (10/5/2010)


    is there a way to make the datetime just a date???

    Convert it to the DATE data type.

  • how do i convert it?

  • 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