Post reply

converting int format to datetimevformat

  • February 3, 2003 at 10:03 pm

    #80632

    hi! guys,

    i'm a beginner doing sql script....

    can anybody help me ....

    all i want to do is to get all transaction from the last 3 months with doc_num starting from 'CI%'

    below is the sample select statement, and the result is " (0) zero rows affected "

    selecta.customer_code,

    a.doc_num,

    date_doc = dateadd (dd, a.date_doc-639906, '1/1/1753') ,

    date_entered = dateadd (dd, a.date_entered-639906, '1/1/1753')

    fromarheader a,

    ardetail b

    wherea.doc_num = b.doc_num

    and a.doc_num like 'ci%'

    and (convert (datetime,a.date_entered) >= dateadd(mm, -3, getdate ())

    and convert (datetime,a.date_entered) <= getdate () )

    Hope u can help ....

    Thanks

    lhavn

  • February 4, 2003 at 3:35 am

    #447940

    Can you provide an example of the value of

    date_doc

    and

    date_entered

    so we can understand what is going on there?

    Also, what do those values represent? (Ex. number of sec. since 1/1/1900 12:00 AM)

  • February 4, 2003 at 3:49 am

    #447944

    When I am interesting in getting out info using a date interval I normally use the convert function like you do, or the datediff function like this...

    ...

    and datediff(mm, a.date_entered, getdate()) >= 3

    ...

    This one-row-statement will replace your to statements. I don't think this solves your problem though.

    //Pagander

  • February 4, 2003 at 3:51 am

    #447945

    Sorry... flip the '>=' to '<=' to get it right...

  • February 4, 2003 at 8:04 am

    #447975

    I'm guessing your date_entered field is the number of days since 01/01/0000. If this is so, then I think you need to try something like this:

    create table test (date_entered int)

    declare @num_days int

    select datediff(dd,'1/1/1753','1/1/2003')

    set @num_days = 639906+91310

    insert into test values (@num_days)

    select datediff(dd,'1/1/1753','10/1/2002')

    set @num_days = 639906+91218

    insert into test values (@num_days)

    select date_entered = dateadd (dd, a.date_entered-639906, '1/1/1753')

    from test a

    where

    dateadd (dd, a.date_entered-639906, '1/1/1753') >= dateadd(mm, -3, getdate ()) and

    dateadd (dd, a.date_entered-639906, '1/1/1753') <= getdate ()

    If not let us know what your data_entered value represents.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

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

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