DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: date function_I am in hurry

  1. #1
    sri Guest

    date function_I am in hurry


    Hi, VB pros:

    I don't know how to use SQL statement to find all the records between two
    dates. Can you give one example? I have a date field, and other
    related fields for my record.I am accpting date with text boxes But when
    I use 'Where' clause (WHERE datefield>=firstdate
    and datefield<=seconddate),the second date to be by default current date
    and first date to be entered by users, it doesn't work. Please give me some
    help. it's giving me message error on converting datatime to chr string.
    sri



  2. #2
    Michael Culley Guest

    Re: date function_I am in hurry


    sri,

    dates must have # around them and be in US format (month first)

    WHERE Date1<#01/23/1999#

    "sri" <sri_kapa@hotmail.com> wrote:
    >
    >Hi, VB pros:
    >
    >I don't know how to use SQL statement to find all the records between two
    >dates. Can you give one example? I have a date field, and other
    >related fields for my record.I am accpting date with text boxes But when
    >I use 'Where' clause (WHERE datefield>=firstdate
    >and datefield<=seconddate),the second date to be by default current date
    >and first date to be entered by users, it doesn't work. Please give me some
    >help. it's giving me message error on converting datatime to chr string.
    >sri
    >
    >



  3. #3
    Daniel Reber Guest

    Re: date function_I am in hurry


    Sri,
    What backend are you using? Access, SQL, Oracle?

    Daniel Reber


    "sri" <sri_kapa@hotmail.com> wrote:
    >
    >Hi, VB pros:
    >
    >I don't know how to use SQL statement to find all the records between two
    >dates. Can you give one example? I have a date field, and other
    >related fields for my record.I am accpting date with text boxes But when
    >I use 'Where' clause (WHERE datefield>=firstdate
    >and datefield<=seconddate),the second date to be by default current date
    >and first date to be entered by users, it doesn't work. Please give me some
    >help. it's giving me message error on converting datatime to chr string.
    >sri
    >
    >



  4. #4
    Tonny H Guest

    Re: date function_I am in hurry


    It seems like you are having trouble with datatypes. You can do as below

    in SQL
    select * from TABLENAME
    where date between convert(datetime, date1) and convert(datetime, date2)

    If the date itself already in format 1/1/00 then you can do
    select * from TABLENAME where date between '1/1/00' and '1/4/00'

    assuming that the table field is datetime field

    -t-

    "sri" <sri_kapa@hotmail.com> wrote:
    >
    >Hi, VB pros:
    >
    >I don't know how to use SQL statement to find all the records between two
    >dates. Can you give one example? I have a date field, and other
    >related fields for my record.I am accpting date with text boxes But when
    >I use 'Where' clause (WHERE datefield>=firstdate
    >and datefield<=seconddate),the second date to be by default current date
    >and first date to be entered by users, it doesn't work. Please give me some
    >help. it's giving me message error on converting datatime to chr string.
    >sri
    >
    >



  5. #5
    SRI Guest

    Re: date function_I am in hurry


    MICHAEL,
    I TRIED THAT ONE, THE FIRST WILL BE ENTERED BY THE USER.
    I CREATE TWO TEXT BOXES FOR DATES, I SET SECOND DATE CURRENT DATE
    I AM DOING LIKE, WHERE CDATE>='TEXT1.TEXT'OR #TEXT1.TEXT# AND CDATE<='TEXT2.TEXT"
    LIKE THAT.WHEN I AM TRYING THIS THE ERROR MESSAGE WAS CONVERTING DATETIME
    FROM CHARACTER STRING.i TRIED SO MANY FORMATS,AND I USING ADODB.
    THANKS,
    SRI.

    "Michael Culley" <m_culley@one.net.au> wrote:
    >
    >sri,
    >
    >dates must have # around them and be in US format (month first)
    >
    >WHERE Date1<#01/23/1999#
    >
    >"sri" <sri_kapa@hotmail.com> wrote:
    >>
    >>Hi, VB pros:
    >>
    >>I don't know how to use SQL statement to find all the records between two
    >>dates. Can you give one example? I have a date field, and other
    >>related fields for my record.I am accpting date with text boxes But when
    >>I use 'Where' clause (WHERE datefield>=firstdate
    >>and datefield<=seconddate),the second date to be by default current date
    >>and first date to be entered by users, it doesn't work. Please give me

    some
    >>help. it's giving me message error on converting datatime to chr string.
    >>sri
    >>
    >>

    >



  6. #6
    SRI Guest

    Re: date function_I am in hurry


    DANIEL,
    IAM USING SQL SERVER AS BACKEND.

    "Daniel Reber" <daniel@domain-group.com> wrote:
    >
    >Sri,
    > What backend are you using? Access, SQL, Oracle?
    >
    >Daniel Reber
    >
    >
    >"sri" <sri_kapa@hotmail.com> wrote:
    >>
    >>Hi, VB pros:
    >>
    >>I don't know how to use SQL statement to find all the records between two
    >>dates. Can you give one example? I have a date field, and other
    >>related fields for my record.I am accpting date with text boxes But when
    >>I use 'Where' clause (WHERE datefield>=firstdate
    >>and datefield<=seconddate),the second date to be by default current date
    >>and first date to be entered by users, it doesn't work. Please give me

    some
    >>help. it's giving me message error on converting datatime to chr string.
    >>sri
    >>
    >>

    >



  7. #7
    SRI Guest

    Re: date function_I am in hurry


    TONNY,
    I TRIED THAT ONE, THE FIRST WILL BE ENTERED BY THE USER.
    I CREATE TWO TEXT BOXES FOR DATES, I SET SECOND DATE CURRENT DATE
    WHEN IAM DOING LIKE YOU TOLD ME,THE MESSAGE WAS THE COLUMN PREFIX TEXT1 DOES
    NOT MATCH WITH A TABLE NAME OR ALIAS NAME USED IN THE QUERY,I AM USING SQL
    SERVER,AND THAT FIELD WAS DATATIME.
    THANKS,
    SRI.


    "Tonny H" <tonnyh@medianetworks.net> wrote:
    >
    >It seems like you are having trouble with datatypes. You can do as below
    >
    >in SQL
    >select * from TABLENAME
    >where date between convert(datetime, date1) and convert(datetime, date2)
    >
    >If the date itself already in format 1/1/00 then you can do
    >select * from TABLENAME where date between '1/1/00' and '1/4/00'
    >
    >assuming that the table field is datetime field
    >
    >-t-
    >
    >"sri" <sri_kapa@hotmail.com> wrote:
    >>
    >>Hi, VB pros:
    >>
    >>I don't know how to use SQL statement to find all the records between two
    >>dates. Can you give one example? I have a date field, and other
    >>related fields for my record.I am accpting date with text boxes But when
    >>I use 'Where' clause (WHERE datefield>=firstdate
    >>and datefield<=seconddate),the second date to be by default current date
    >>and first date to be entered by users, it doesn't work. Please give me

    some
    >>help. it's giving me message error on converting datatime to chr string.
    >>sri
    >>
    >>

    >



  8. #8
    joe maki Guest

    Re: date function_I am in hurry


    You need to concatenate your text box values into the SQL string:

    strSQL = "SELECT * FROM SOMETABLE" & vbcrlf & _
    "WHERE DATEFIELD BETWEEN '" & TEXT1.TEXT & "'" & _
    "AND '" & TEXT2.TEXT & "'"

    This way the code will resolve your textbox values as the values entered
    in them:

    SELECT * FROM SOMETABLE
    WHERE DATEFIELD BETWEEN '01/01/00' AND '01/15/00'

    ... joe maki

    "SRI" <SRI.KAPA@FIGTREESYS.COM.AU> wrote:
    >
    >TONNY,
    >I TRIED THAT ONE, THE FIRST WILL BE ENTERED BY THE USER.
    >I CREATE TWO TEXT BOXES FOR DATES, I SET SECOND DATE CURRENT DATE
    >WHEN IAM DOING LIKE YOU TOLD ME,THE MESSAGE WAS THE COLUMN PREFIX TEXT1

    DOES
    >NOT MATCH WITH A TABLE NAME OR ALIAS NAME USED IN THE QUERY,I AM USING SQL
    >SERVER,AND THAT FIELD WAS DATATIME.
    >THANKS,
    >SRI.
    >
    >
    >"Tonny H" <tonnyh@medianetworks.net> wrote:
    >>
    >>It seems like you are having trouble with datatypes. You can do as below
    >>
    >>in SQL
    >>select * from TABLENAME
    >>where date between convert(datetime, date1) and convert(datetime, date2)
    >>
    >>If the date itself already in format 1/1/00 then you can do
    >>select * from TABLENAME where date between '1/1/00' and '1/4/00'
    >>
    >>assuming that the table field is datetime field
    >>
    >>-t-
    >>
    >>"sri" <sri_kapa@hotmail.com> wrote:
    >>>
    >>>Hi, VB pros:
    >>>
    >>>I don't know how to use SQL statement to find all the records between

    two
    >>>dates. Can you give one example? I have a date field, and other
    >>>related fields for my record.I am accpting date with text boxes But when
    >>>I use 'Where' clause (WHERE datefield>=firstdate
    >>>and datefield<=seconddate),the second date to be by default current date
    >>>and first date to be entered by users, it doesn't work. Please give me

    >some
    >>>help. it's giving me message error on converting datatime to chr string.
    >>>sri
    >>>
    >>>

    >>

    >



  9. #9
    sri Guest

    Re: date function_I am in hurry


    joe maki
    Thanks for that date syntax but know I am some other problem like runtime
    error3709 the application requested an object with a reference to a closed
    or invalid connection object.
    I am using runtime adodb with datagrid.
    sri


    "joe maki" <joewmaki@hotmail.com> wrote:
    >
    >You need to concatenate your text box values into the SQL string:
    >
    >strSQL = "SELECT * FROM SOMETABLE" & vbcrlf & _
    > "WHERE DATEFIELD BETWEEN '" & TEXT1.TEXT & "'" & _
    > "AND '" & TEXT2.TEXT & "'"
    >
    >This way the code will resolve your textbox values as the values entered
    >in them:
    >
    >SELECT * FROM SOMETABLE
    >WHERE DATEFIELD BETWEEN '01/01/00' AND '01/15/00'
    >
    >... joe maki
    >
    >"SRI" <SRI.KAPA@FIGTREESYS.COM.AU> wrote:
    >>
    >>TONNY,
    >>I TRIED THAT ONE, THE FIRST WILL BE ENTERED BY THE USER.
    >>I CREATE TWO TEXT BOXES FOR DATES, I SET SECOND DATE CURRENT DATE
    >>WHEN IAM DOING LIKE YOU TOLD ME,THE MESSAGE WAS THE COLUMN PREFIX TEXT1

    >DOES
    >>NOT MATCH WITH A TABLE NAME OR ALIAS NAME USED IN THE QUERY,I AM USING

    SQL
    >>SERVER,AND THAT FIELD WAS DATATIME.
    >>THANKS,
    >>SRI.
    >>
    >>
    >>"Tonny H" <tonnyh@medianetworks.net> wrote:
    >>>
    >>>It seems like you are having trouble with datatypes. You can do as below
    >>>
    >>>in SQL
    >>>select * from TABLENAME
    >>>where date between convert(datetime, date1) and convert(datetime, date2)
    >>>
    >>>If the date itself already in format 1/1/00 then you can do
    >>>select * from TABLENAME where date between '1/1/00' and '1/4/00'
    >>>
    >>>assuming that the table field is datetime field
    >>>
    >>>-t-
    >>>
    >>>"sri" <sri_kapa@hotmail.com> wrote:
    >>>>
    >>>>Hi, VB pros:
    >>>>
    >>>>I don't know how to use SQL statement to find all the records between

    >two
    >>>>dates. Can you give one example? I have a date field, and other
    >>>>related fields for my record.I am accpting date with text boxes But when
    >>>>I use 'Where' clause (WHERE datefield>=firstdate
    >>>>and datefield<=seconddate),the second date to be by default current date
    >>>>and first date to be entered by users, it doesn't work. Please give me

    >>some
    >>>>help. it's giving me message error on converting datatime to chr string.
    >>>>sri
    >>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center