Professional Web Applications Themes

Compare Date problem - Microsoft SQL / MS SQL Server

I have 3 integer columns in my table, which represents the date of entry: dayEntry monthEntry yearEntry How can I combine this 3 columns to get the real date and then use it in my query as condition: set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1, GETDATE()), 112) .... WHERE dateEntry>=orderDae Something like this: ....WHERE (dayEntry+monthEntry+yearEntry)>=orderDate Thank you for your answer, Simon...

  1. #1

    Default Compare Date problem

    I have 3 integer columns in my table, which represents the date of entry:
    dayEntry monthEntry yearEntry

    How can I combine this 3 columns to get the real date and then use it in my
    query as condition:

    set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1,
    GETDATE()), 112)

    .... WHERE dateEntry>=orderDae
    Something like this:

    ....WHERE (dayEntry+monthEntry+yearEntry)>=orderDate

    Thank you for your answer,
    Simon


    Simon Guest

  2. #2

    Default Re: Compare Date problem

    DATEADD(dd, dayEntry-1, DATEADD(mm, monthEntry -1
    ,DATEADD(yy,yearEntry -1900,'19000101')))

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f0d2dd4$1news.s5.net...
    > I have 3 integer columns in my table, which represents the date of entry:
    > dayEntry monthEntry yearEntry
    >
    > How can I combine this 3 columns to get the real date and then use it in
    my
    > query as condition:
    >
    > set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1,
    > GETDATE()), 112)
    >
    > ... WHERE dateEntry>=orderDae
    > Something like this:
    >
    > ...WHERE (dayEntry+monthEntry+yearEntry)>=orderDate
    >
    > Thank you for your answer,
    > Simon
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Compare Date problem

    Simon,

    You can use implicit conversions relying on "unseparated string format" ( see books online ):

    declare yr int, mn int, dy int
    set yr = 2003
    set mn = 11
    set dy = 23

    select *
    from pubs..sales
    where ord_date < cast( cast(yr as varchar(4)) + cast(mn as varchar(2)) + cast(dy as varchar(2)) as
    datetime )

    Some issues you may not be aware of:
    1) This where clause will not use an index if you fit it to your application by substituting columns for
    variables.
    2) Storing dates split into several columns is pretty hard to justify from a design point of view.
    3) If ranges of the columns do not relate to a valid date - and a constraint that a normal
    human could write won't enforce that, then you will get a nasty runtime error on the convert.

    You may sort of get some sanity / performance back ( if you really are stuck with separate columns for date )
    by adding a calculated datetime column, indexed and constrainted as need be, based on the above calculation
    (I haven't tried it myself yet)

    Regards
    AJ

    "Simon" <simon.zupanstud-moderna.si> wrote in message news:3f0d2dd4$1news.s5.net...
    > I have 3 integer columns in my table, which represents the date of entry:
    > dayEntry monthEntry yearEntry
    >
    > How can I combine this 3 columns to get the real date and then use it in my
    > query as condition:
    >
    > set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1,
    > GETDATE()), 112)
    >
    > ... WHERE dateEntry>=orderDae
    > Something like this:
    >
    > ...WHERE (dayEntry+monthEntry+yearEntry)>=orderDate
    >
    > Thank you for your answer,
    > Simon
    >
    >

    Andrew John Guest

  4. #4

    Default Re: Compare Date problem

    Hi,

    it works, but query is extremly slow.
    Do you have some other suggestion?

    Thank you,
    Simon

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:OlKdmUsRDHA.3192tk2msftngp13.phx.gbl...
    > DATEADD(dd, dayEntry-1, DATEADD(mm, monthEntry -1
    > ,DATEADD(yy,yearEntry -1900,'19000101')))
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > news:3f0d2dd4$1news.s5.net...
    > > I have 3 integer columns in my table, which represents the date of
    entry:
    > > dayEntry monthEntry yearEntry
    > >
    > > How can I combine this 3 columns to get the real date and then use it in
    > my
    > > query as condition:
    > >
    > > set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE())
    +1,
    > > GETDATE()), 112)
    > >
    > > ... WHERE dateEntry>=orderDae
    > > Something like this:
    > >
    > > ...WHERE (dayEntry+monthEntry+yearEntry)>=orderDate
    > >
    > > Thank you for your answer,
    > > Simon
    > >
    > >
    >
    >

    Simon Guest

  5. #5

    Default Re: Compare Date problem


    Convert your three fields in a date with format 'yyyymmdd'. Instead of
    integer, you should have varchar to avoid an explicit cast.

    SELECT 1 WHERE (charYear + charMonth + charDay) >= CONVERT(CHAR(8),
    DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1, GETDATE()), 112)



    --
    --

    Un saludo

    --
    --
    ----------------------------------------------
    "Sólo sé que no sé nada. " (Sócrates)

    Por favor, responder únicamente al foro

    (Guía de netiquette del foro)
    [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
    [url]http://perso.wanadoo.es/rubenvigon/foro[/url]

    (FAQ's de SQL Server)
    [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
    [url]http://www.helpdna.net/bosqlfaq.htm[/url]

    "Simon" <simon.zupanstud-moderna.si> escribió en el mensaje
    news:3f0d342a$1news.s5.net...
    > Hi,
    >
    > it works, but query is extremly slow.
    > Do you have some other suggestion?
    >
    > Thank you,
    > Simon
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:OlKdmUsRDHA.3192tk2msftngp13.phx.gbl...
    > > DATEADD(dd, dayEntry-1, DATEADD(mm, monthEntry -1
    > > ,DATEADD(yy,yearEntry -1900,'19000101')))
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > > news:3f0d2dd4$1news.s5.net...
    > > > I have 3 integer columns in my table, which represents the date of
    > entry:
    > > > dayEntry monthEntry yearEntry
    > > >
    > > > How can I combine this 3 columns to get the real date and then use it
    in
    > > my
    > > > query as condition:
    > > >
    > > > set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE())
    > +1,
    > > > GETDATE()), 112)
    > > >
    > > > ... WHERE dateEntry>=orderDae
    > > > Something like this:
    > > >
    > > > ...WHERE (dayEntry+monthEntry+yearEntry)>=orderDate
    > > >
    > > > Thank you for your answer,
    > > > Simon
    > > >
    > > >
    > >
    > >
    >
    >

    Carlos Sacristan Guest

  6. #6

    Default Re: Compare Date problem

    Go with the calculated indexed column approach as suggested by Andrew.

    ALTER TABLE MyTable ADD orderDate AS DATEADD(dd, dayEntry-1, DATEADD(mm,
    monthEntry -1
    ,DATEADD(yy,yearEntry -1900,'19000101')))

    CREATE NONCLUSTERED INDEX ix_orderDate ON MyTable (orderDate)

    and in your code: WHERE dateEntry >= orderDate

    Don't use his code btw, he forgot to add leading zeros to the month and date
    ;-)


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f0d342a$1news.s5.net...
    > Hi,
    >
    > it works, but query is extremly slow.
    > Do you have some other suggestion?
    >
    > Thank you,
    > Simon
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:OlKdmUsRDHA.3192tk2msftngp13.phx.gbl...
    > > DATEADD(dd, dayEntry-1, DATEADD(mm, monthEntry -1
    > > ,DATEADD(yy,yearEntry -1900,'19000101')))
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > > news:3f0d2dd4$1news.s5.net...
    > > > I have 3 integer columns in my table, which represents the date of
    > entry:
    > > > dayEntry monthEntry yearEntry
    > > >
    > > > How can I combine this 3 columns to get the real date and then use it
    in
    > > my
    > > > query as condition:
    > > >
    > > > set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE())
    > +1,
    > > > GETDATE()), 112)
    > > >
    > > > ... WHERE dateEntry>=orderDae
    > > > Something like this:
    > > >
    > > > ...WHERE (dayEntry+monthEntry+yearEntry)>=orderDate
    > > >
    > > > Thank you for your answer,
    > > > Simon
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  7. #7

    Default Re: Compare Date problem

    Hi,

    sorry, but I can't change my database fields to varchar, so I have to
    explicit convert every field to varchar and it takes a lot of time.
    Instead of that, I found faster solution:

    DECLARE dan int, mesec int, leto int,danS int, mesecS int, letoS int
    DECLARE dt DATETIME, datumTeden datetime

    set datumTeden=DATEADD(dd, -1*DATEPART(dw, GETDATE()) +2,GETDATE())
    set danS=day(getdate())
    set mesecS=month(getdate())
    set letoS=year(getdate())
    set dan=day(datumTeden)
    set mesec=month(datumTeden)
    set leto=year(datumTeden)

    SELECT sum(quantity)as weekQuantity,c1.rco,c1.id_izdelka
    from [olap-server].[DW_Temp].[dbo].[t_post_narocilnice_cube] c1
    WHERE
    ((c1.day >=dan AND c1.month = mesec AND c1.year=leto)
    OR (c1.day<=danS AND c1.month=mesecS AND c1.year=letoS))
    group by c1.rco,c1.id_izdelka

    Does anybody know how to improve this?

    Thank you all,
    Simon

    "Carlos Sacristan" <csacristanARROBAocasoPUNTOes> wrote in message
    news:eSrMsksRDHA.2008TK2MSFTNGP11.phx.gbl...
    >
    > Convert your three fields in a date with format 'yyyymmdd'. Instead of
    > integer, you should have varchar to avoid an explicit cast.
    >
    > SELECT 1 WHERE (charYear + charMonth + charDay) >= CONVERT(CHAR(8),
    > DATEADD(dd, -1*DATEPART(dw, GETDATE()) +1, GETDATE()), 112)
    >
    >
    >
    > --
    > --
    >
    > Un saludo
    >
    > --
    > --
    > ----------------------------------------------
    > "Sólo sé que no sé nada. " (Sócrates)
    >
    > Por favor, responder únicamente al foro
    >
    > (Guía de netiquette del foro)
    > [url]http://www.helpdna.net/bosqlfaq00.htm[/url]
    > [url]http://perso.wanadoo.es/rubenvigon/foro[/url]
    >
    > (FAQ's de SQL Server)
    > [url]http://support.microsoft.com/default.aspx?scid=/support/sql/70faq.asp[/url]
    > [url]http://www.helpdna.net/bosqlfaq.htm[/url]
    >
    > "Simon" <simon.zupanstud-moderna.si> escribió en el mensaje
    > news:3f0d342a$1news.s5.net...
    > > Hi,
    > >
    > > it works, but query is extremly slow.
    > > Do you have some other suggestion?
    > >
    > > Thank you,
    > > Simon
    > >
    > > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > > news:OlKdmUsRDHA.3192tk2msftngp13.phx.gbl...
    > > > DATEADD(dd, dayEntry-1, DATEADD(mm, monthEntry -1
    > > > ,DATEADD(yy,yearEntry -1900,'19000101')))
    > > >
    > > > --
    > > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > > Database Administrator
    > > > Eurostop Ltd.
    > > >
    > > >
    > > > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > > > news:3f0d2dd4$1news.s5.net...
    > > > > I have 3 integer columns in my table, which represents the date of
    > > entry:
    > > > > dayEntry monthEntry yearEntry
    > > > >
    > > > > How can I combine this 3 columns to get the real date and then use
    it
    > in
    > > > my
    > > > > query as condition:
    > > > >
    > > > > set orderDate=CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw,
    GETDATE())
    > > +1,
    > > > > GETDATE()), 112)
    > > > >
    > > > > ... WHERE dateEntry>=orderDae
    > > > > Something like this:
    > > > >
    > > > > ...WHERE (dayEntry+monthEntry+yearEntry)>=orderDate
    > > > >
    > > > > Thank you for your answer,
    > > > > Simon
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Simon Guest

  8. #8

    Default Re: Compare Date problem

    Oops,

    Bad test data, bad.

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:uoGeBlsRDHA.3880tk2msftngp13.phx.gbl...
    >
    > Don't use his code btw, he forgot to add leading zeros to the month and date
    > ;-)
    >

    Andrew John Guest

Similar Threads

  1. date compare
    By TurboMini in forum Coldfusion - Getting Started
    Replies: 8
    Last Post: June 29th, 06:48 AM
  2. Compare current date with DWfile.getCreationDate/Obj
    By Jammer in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 4
    Last Post: October 13th, 05:03 PM
  3. compare a date in db field to today
    By Darren Heinrich in forum ASP Database
    Replies: 8
    Last Post: February 8th, 06:32 AM
  4. compare date on cd to system date
    By Rowan Ferguson in forum Macromedia Director Lingo
    Replies: 2
    Last Post: October 2nd, 04:41 AM
  5. compare date,time
    By Awards in forum PERL Beginners
    Replies: 5
    Last Post: August 27th, 06:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139