Professional Web Applications Themes

date problem - Coldfusion - Getting Started

I have date field in my table: 2003-06-29 02:21:19.373 Now I would like to get the records from my table: SELECT * FROM table WHERE date='06-29-2003' but it doesn't work. I get the result only if I write the select like this: SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003' Is there any easier solution? I have table with more then a million records and if I use dateadd(date-1) and dateadd(date+1), the execution would be slower. Thank you, Simon...

Sponsored Links
  1. #1

    Default Date problem

    I have date field in my table:
    2003-06-29 02:21:19.373

    Now I would like to get the records from my table:

    SELECT * FROM table WHERE date='06-29-2003'

    but it doesn't work.

    I get the result only if I write the select like this:


    SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003'

    Is there any easier solution?
    I have table with more then a million records and if I use dateadd(date-1)
    and dateadd(date+1),
    the execution would be slower.

    Thank you,
    Simon


    Sponsored Links
    Simon Guest

  2. #2

    Default Re: Date problem

    The second SELECT statement you posted will include dates from 2003-06-28. I
    think you wanted just one day:

    SELECT * FROM table WHERE date>='20030629' AND date<'20030630'

    Always use ISO (YYYYMMDD) format for dates to avoid dependencies on regional
    settings

    As you point out, other solutions to extract just the date portion of the
    DATETIME will be slower because they cannot benefit from indexing on the
    column. Above is the best method.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  3. #3

    Default Re: Date problem

    select * from table where convert(varchar(8),date,112)='20030629'
    HTH
    Falik


    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f0a6be6$1news.s5.net...
    > I have date field in my table:
    > 2003-06-29 02:21:19.373
    >
    > Now I would like to get the records from my table:
    >
    > SELECT * FROM table WHERE date='06-29-2003'
    >
    > but it doesn't work.
    >
    > I get the result only if I write the select like this:
    >
    >
    > SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003'
    >
    > Is there any easier solution?
    > I have table with more then a million records and if I use dateadd(date-1)
    > and dateadd(date+1),
    > the execution would be slower.
    >
    > Thank you,
    > Simon
    >
    >

    Falik Sher Guest

  4. #4

    Default Re: Date problem

    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f0a6be6$1news.s5.net...
    > I have date field in my table:
    > 2003-06-29 02:21:19.373
    >
    > Now I would like to get the records from my table:
    >
    > SELECT * FROM table WHERE date='06-29-2003'
    >
    > but it doesn't work.
    >
    > I get the result only if I write the select like this:
    >
    >
    > SELECT * FROM table WHERE date>'06-28-2003' AND date<'06-30-2003'
    >
    > Is there any easier solution?
    > I have table with more then a million records and if I use dateadd(date-1)
    > and dateadd(date+1),
    > the execution would be slower.
    >
    > Thank you,
    > Simon
    >
    >
    try this:

    select * from table where convert(char(10), date, 120 ) = '2003-06-29'

    /johan


    johan olofsson Guest

  5. #5

    Default Re: Date problem

    [posted and mailed, please reply in news]

    Falik Sher (falikshotmail.com) writes:
    > select * from table where convert(varchar(8),date,112)='20030629'
    While this gives the desired result, it has the drawback that if there
    is an index on the date column, SQL Server will not use the index on
    the column, since you use it in an expression.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  6. #6

    Default date problem

    Hi,

    Why does the following code print '00', surely it should print '08', I'm
    baffled!

    date("H", mktime(8, 0, 0, 0, 0, 0));

    Thanks for your help
    Shaun Guest

  7. #7

    Default Re: date problem

    For me, on Windows, it won't work because Windows won't do anything prior to
    1970.

    On linux, I get 17 as the result. If I change the year to 2000, then I get
    08 on both.

    John


    "Shaun" <shaunmania.plus.com> wrote in message
    news:20030924160719.11919.qmailpb1.pair.com...
    > Hi,
    >
    > Why does the following code print '00', surely it should print '08', I'm
    > baffled!
    >
    > date("H", mktime(8, 0, 0, 0, 0, 0));
    >
    > Thanks for your help
    John Guest

  8. #8

    Default date problem

    I have a field in database where I am inputting the value as
    <cfset tday = #DateFormat(CreateODBCDate(now()), "mm-dd-yyyy")#>

    Now I need to query so that i can get the data b/w any two dates. How should i
    query it.
    I was doing
    select * from table1 where tdate > 'somedate1' and tdate <'somedate2'

    it keeps on giving me 0 records



    trycold Guest

  9. #9

    Default Re: date problem

    It could be you need to create actual date objects rather than strings of dates
    - for example your message shows you using dateformat for the date entered into
    the database, this would give you a string rather than a date.

    babyeatingreptile Guest

  10. #10

    Default Re: date problem

    It depends on the database, in Oracle you would:
    SELECT *
    FROM table_t
    WHERE tdate between '#DateFormat(date1, "dd-mm-yy")#'
    and '#DateFormat(date2, "dd-mm-yy")#'

    you should probably use either DateFormat or CreateODBCDate, not both.
    Also, you dont need to use the #'s inside cf tags, it creates an extra
    processing step.

    kyle969 Guest

  11. #11

    Default Date Problem

    Hi,

    I'm being plagued by a date problem. I use an access database to store my site
    data in and have had no problem on the whole, but the one problem I do get it
    is with storing dates.

    At first I was okay and the dates went in without problem. Then all of a
    sudden the dates reversed i.e. 10/5/2005 became 05/10/2005

    I got round this by moving from the cfinsert tag to using

    <cfquery datasource="tbsdata" name="updatenews">
    UPDATE news
    SET
    dateadded='#dateformat(dateadded,"dd/mm/yyyy")#',title='#title#',body='#body#',t
    ype='#type#',username='#username#'
    WHERE id = #id#
    </cfquery>

    now this has suddenly started to reverse the code. for the time being I'm
    using #dateformat(dateadded,"mm/dd/yyyy")# to solve the problem but it's a poor
    solution.

    Does anyone have any ideas why ? The table has the dateadded feild setup as a
    date/time type. Would making the type text be a better idea or would this
    course problems?

    Cheers,

    Craig.

    Ginga2050 Guest

  12. #12

    Default Re: Date Problem

    You do not want to use dates in this manner. Application wide you should use
    dates in the ODBC Date format, which is how the date looks when you output the
    Now() function.

    Only use DateFormat() to display dates in the browser.

    To make an ODBC compliant date from its parts use CreateDate(year, month, day).
    To make a date that is complete use CreateODBCDate().

    NEVER insert a date that is not in this format into your database as you are
    asking for trouble. I hope that helps, if you want to know more look and the
    date and time functions on LiveDocs:-

    [url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/functio6.htm#wp1098968[/url]

    Stressed_Simon Guest

  13. #13

    Default Re: Date Problem

    Cheers. I get what you mean but I only went down that route to stop the
    americanized dates. Will the methos that you suggest stop this. The problem is
    now affecting to seperate servers that have been setup with UK locales etc.



    Ginga2050 Guest

  14. #14

    Default Re: Date Problem

    Well I am not a DBA but I am pretty sure that Databases store dates identically
    regardless of the resional settings, it is just how they display if you view
    them that is defined by the regional settings. If you use date format to show
    the dates how you want in the browser, their should not be a problem. You may
    want to set you locale though as this unlocks all the LS functions.

    Stressed_Simon Guest

  15. #15

    Default Re: Date Problem

    Tried your suggestion and same problem.

    With my method I am using I am getting the full date / time stamp as I would
    with outputting #now()#

    It look to be a problem when the data actually hits the database. I
    thinkAccess might be messing the date up.

    Ginga2050 Guest

  16. #16

    Default Re: Date Problem

    All CF date functions, including CreateODBCDate() expect months first strings,
    and will create
    date objects accordingly. What you need to do is this:

    <cfset TheDAte = "1/2/2005"> **** 2 Feb 2005 in UK ****
    <cfset x = SetLocale("English (UK)")>
    <cfset UKDate = LSPDAteTime(TheDate)>
    <cfoutput>#UKDate#</cfoutput>

    Now put UKDate into the DB directly because it's a date object. Don't try
    to reformat it unless it's for display only.


    OldCFer Guest

  17. #17

    Default Date problem

    Hi,

    I have a form that registers users on my system, but I need them to enter
    their Date of birth.

    I would like to populate a set of drop downs, so users can enter their day,
    month and year of birth. Could anyone tell me how to get the current date and
    use this to populate the lists?

    I would also like to make it as fool proof as possible. How can I check to see
    if they enter a valid date?

    Hope you can help

    Mattastic Guest

  18. #18

    Default Re: Date problem

    You can obtain the current date using the Now() function.

    [url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
    wwhelp.htm?context=ColdFusion_Doentation&file=p art_cfm.htm

    Use the IsDate() function to check on the validity of a date.

    [url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
    wwhelp.htm?context=ColdFusion_Doentation&file=p art_cfm.htm

    jdeline Guest

  19. #19

    Default Re: Date problem

    Thanks
    Mattastic Guest

  20. #20

    Default Re: Date problem

    Remember to check that it is not only a valid date, but that it is not in the future.
    Dan Bracuk Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. date query problem, date in variable
    By roy in forum MySQL
    Replies: 1
    Last Post: October 16th, 11:57 AM
  2. PHP Date Problem
    By Vincent J. Gullotta in forum PHP Development
    Replies: 4
    Last Post: February 4th, 10:00 PM
  3. [PHP] date problem
    By Robert Cummings in forum PHP Development
    Replies: 0
    Last Post: September 24th, 05:33 PM
  4. Problem with date('w')
    By Beoi 7308 in forum PHP Development
    Replies: 2
    Last Post: August 25th, 11:08 PM
  5. Compare Date problem
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 11th, 10:19 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