Professional Web Applications Themes

DATETIME - Microsoft SQL / MS SQL Server

hie, i have a statement in ms access and would like to transfer it to sql server. My original statement goes like this: Between #6/29/2003# And #7/5/2003# Currently, i am using this: CONVERT(DATETIME, '2003-06-29 00:00:00', 102) And CONVERT (DATETIME, '2003-07-05 00:00:00', 102) Can i juz omit the "00:00:00" as i juz wish to display the date and not the time? Also, what is the meaning behind the number 102 and where can i get more information regarding this? thanx!...

  1. #1

    Default DATETIME

    hie, i have a statement in ms access and would like to
    transfer it to sql server. My original statement goes like
    this:
    Between #6/29/2003# And #7/5/2003#

    Currently, i am using this:
    CONVERT(DATETIME, '2003-06-29 00:00:00', 102) And CONVERT
    (DATETIME, '2003-07-05 00:00:00', 102)

    Can i juz omit the "00:00:00" as i juz wish to display the
    date and not the time? Also, what is the meaning behind
    the number 102 and where can i get more information
    regarding this?

    thanx!
    Anne Guest

  2. #2

    Default Re: DATETIME

    > Currently, i am using this:
    > CONVERT(DATETIME, '2003-06-29 00:00:00', 102) And CONVERT
    > (DATETIME, '2003-07-05 00:00:00', 102)
    You should use

    dateColumn >= '20030629' AND dateColumn < '20030706'

    (You don't need to convert a date to datetime, the query will do that for
    you... and it makes your clause much easier to read.)

    To see why I avoid the BETWEEN clause for dates:
    [url]http://www.aspfaq.com/2280[/url]
    > date and not the time? Also, what is the meaning behind
    > the number 102 and where can i get more information
    > regarding this?
    There is at least a cheat sheet you can build on your own system:
    [url]http://www.aspfaq.com/2464[/url]

    Also see this function, for developing a more intuitive function for date
    presentation:
    [url]http://www.aspfaq.com/2460[/url]


    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: DATETIME


    "JDPWork" <JPgmtNoSpamadelphia.net> wrote in message
    news:#ehbDmvRDHA.2228tk2msftngp13.phx.gbl...
    > > Can i juz omit the "00:00:00" ....
    >
    > Yes, if mydate is a datetime column.
    >
    > ...where mydate between '06/29/2003' and '07/05/2003'
    >
    > I think that this is an implicit conversion. If it looks like a date and
    walks
    > like a date....
    then it will walk in the USA and stumble in Europe :-)

    Always use yyyymmdd as the dateformat, it will be interpreted independent of
    any regional/dateformat/language settings.
    >
    > 102 is yy.mm.dd
    >
    > Check out Query yzer Help for 'Convert'.
    >
    > HTH
    >
    > JeffP....
    >
    > "Anne" <lilanne_2> wrote in message
    > news:014e01c346f8$3fd12250$a101280aphx.gbl...
    > > hie, i have a statement in ms access and would like to
    > > transfer it to sql server. My original statement goes like
    > > this:
    > > Between #6/29/2003# And #7/5/2003#
    > >
    > > Currently, i am using this:
    > > CONVERT(DATETIME, '2003-06-29 00:00:00', 102) And CONVERT
    > > (DATETIME, '2003-07-05 00:00:00', 102)
    > >
    > > Can i juz omit the "00:00:00" as i juz wish to display the
    > > date and not the time? Also, what is the meaning behind
    > > the number 102 and where can i get more information
    > > regarding this?
    > >
    > > thanx!
    >
    >

    Jacco Schalkwijk Guest

  4. #4

    Default Re: Datetime

    Nikola,

    In your ASP application convert fldDate to "yyyy-mm-dd" format , before you
    add it to where clause.

    HTH,
    Praveen
    MCSD, MCDBA

    "Nikola" <com> wrote in message
    news:beu5f7$lla$iskon.hr... 


    praveen Guest

  5. #5

    Default Re: Datetime

    Convert the date explicitly to a datetime (104 is the German date format):

    "(Date_time = CONVERT(datetime," & Request("fldDate") & ", 104 ) "

    Or even better, use a stored procedure so you can pass a datetime parameter
    instead of passing the date as text.

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


    "Nikola" <com> wrote in message
    news:beu5f7$lla$iskon.hr... 


    Jacco Guest

  6. #6

    Default Re: Datetime

    if you are not passing format of yyyy-mm-dd
    then you will have to direct sql server in which format the datetime field
    is passed using CONVERT function.
    if it is dd.mm.yyyy then use following format value of 104, your SQL query
    will look something like this.
    Ex:
    SELECT * FROM table WHERE Datum = convert(datetime,'25.03.2003',104)

    --
    -Vishal
    "Nikola" <com> wrote in message
    news:beu5f7$lla$iskon.hr... 


    Vishal Guest

  7. #7

    Default Re: Datetime

    In addition to the other responses, it appears you forgot to enclose the
    date in single quotes in your VBScript code: Should be:

    sql = sql & "WHERE "
    sql = sql & "(Date_time = '" & Request("fldDate") & "' ) "


    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Nikola" <com> wrote in message
    news:beu5f7$lla$iskon.hr... 
    '2003-02-25' 


    Dan Guest

  8. #8

    Default Re: Datetime

    > if you are not passing format of yyyy-mm-dd 

    Actually, yyyymmdd is much safer than yyyy-mm-dd.

    SET DATEFORMAT MDY
    SELECT ISDATE('2003-02-13') -- 1
    SELECT ISDATE('20030213') -- 1

    SET DATEFORMAT DMY
    SELECT ISDATE('2003-02-13') -- 0
    SELECT ISDATE('20030213') -- 1


    Aaron Guest

  9. #9

    Default Re: DATETIME

    When using a between

    between '06/29/2003' and '07/05/2003'

    will see the value 06/30/2002 as part of that range. The reason is it
    interprets the data as string comparison not numeric. It is better to
    explicitly cast data to the correct type and not let the query engine handle
    implicit typing. A major reason is some implicit typing does not work the
    same under both SQL 7 and SQL 2K. You will be far safer for upgrades and
    data retrieval if you are explicit in typing.

    Also, CONVERT actually will type to a CHAR/VARCHAR value when you use format
    otherwise format is ignored or causes a syntax error. If you want to compare
    with a time of midnight just supply the datepart and it will assume midnight
    since the time will be 0.

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:um%phx.gbl... 
    > >
    > > Yes, if mydate is a datetime column.
    > >
    > > ...where mydate between '06/29/2003' and '07/05/2003'
    > >
    > > I think that this is an implicit conversion. If it looks like a date[/ref][/ref]
    and 
    >
    > then it will walk in the USA and stumble in Europe :-)
    >
    > Always use yyyymmdd as the dateformat, it will be interpreted independent[/ref]
    of 
    > >
    > >[/ref]
    >
    >[/ref]


    James Guest

  10. #10

    Default DATETIME

    Below SQL works

    declare startdate datetime, enddate datetime, sql varchar(1000)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select top 10 * from mytable where mydate between startdate and enddate

    But , below one error out with message
    Server: Msg 241, Level 16, State 1, Line 4
    Syntax error converting datetime from character string.

    declare startdate datetime, enddate datetime, sql varchar(1000)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select sql = 'select * from mytable where mydate between ' + startdate +
    ' AND ' + enddate
    execute sql

    Here startdate and enddate are parameters and used inside a SP.
    I have to use dynamic SQL for my logic and don't want to use CONVERT
    function.
    How to make this dynamic SQL work ??

    Thx
    Sh




    Shamim Guest

  11. #11

    Default Re: DATETIME

    I tried doing this by:
    declare startdate varchar(20), enddate varchar(20), sql varchar(1000)
    select startdate = '07/01/2003'
    select enddate = '07/16/2003'
    select sql = 'select * from mytable where mydate between ''' +
    startdate +
    ''' AND ''' + enddate + ''''
    select SQL
    exec(sql)
    But then for this, your dates are being considered as strings. Is this
    going to be helpful?

    AnsG

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    AnsG Guest

  12. #12

    Default Re: DATETIME


    Tried this , but same error

    declare startdate datetime, enddate datetime,
    sql varchar(255)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select sql = 'select top 10 * from eis_arsdata where chrg_date between
    "' + startdate + '" AND "' + enddate + '"'
    execute sql


    Error Message
    Server: Msg 241, Level 16, State 1, Line 4
    Syntax error converting datetime from character string.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Shamim Guest

  13. #13

    Default DATETIME

    try this (you have to enclose your datetime values in
    quotes):

    declare startdate datetime, enddate datetime, sql
    varchar(1000)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select sql = 'select * from mytable where mydate
    between ''' + startdate +
    ''' AND ''' + enddate + ''''
    execute sql

    Lour Guest

  14. #14

    Default DATETIME

    Hello

    I am not sure I understand the problem.
    When you pass startdate and enddate to proc as parms why
    you can not use them in a where clause?
    What do I miss?

    Gene.

     
    varchar(1000) 
    startdate and enddate 
    varchar(1000) 
    between ' + startdate + 
    inside a SP. 
    use CONVERT 
    gene Guest

  15. #15

    Default Re: DATETIME

    startdate and enddate are parms to my SP.
    So that means, I have to convert that to varchar ???

    Thx
    Sh


     
    sql varchar(1000) 
    between ''' + 
    strings. Is this 
    *** 
    Sh Guest

  16. #16

    Default Re: DATETIME

    Shamin,

    the following code should work:

    declare startdate datetime, enddate datetime, sql varchar(1000)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select sql = 'select * from MyTable where MyTime between '''
    + cast(startdate as varchar) + ''' AND ''' + cast (enddate as varchar) +
    ''''
    execute (sql)

    hope this helps

    Quentin

    "Shamim" <com> wrote in message
    news:#phx.gbl... 
    enddate 



    Quentin Guest

  17. #17

    Default Re: DATETIME



    This Select statement is inside my SP , where I need to build
    dynamically for a different reason .

    Sh

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Shamim Guest

  18. #18

    Default Re: DATETIME

    Sorry , pls read as

    declare startdate datetime, enddate datetime, sql
    varchar(255)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select sql = 'select top 10 * from mytable where mydate
    between "' + startdate + '" AND "' + enddate + '"'
    execute sql

    But same error

    ** It will work , if startdate and enddate changed to
    varchar()

    Thx
    Sh



     
    chrg_date between 
    *** 
    sh Guest

  19. #19

    Default Re: DATETIME

    Thanks for the reply.
    I tried , but with error mesaage..

    Sh
     
    varchar(1000) 
    between ''' 
    (enddate as varchar) + [/ref]
    varchar(1000) [/ref]
    startdate and [/ref]
    varchar(1000) [/ref]
    between ' + startdate [/ref]
    inside a SP. [/ref]
    to use CONVERT 
    >
    >
    >.
    >[/ref]
    Sh Guest

  20. #20

    Default Re: DATETIME

    First off why do you need to use dynamic SQL if you are just replacing the
    date values. Secondly if you must you do not need to use double quotes (")
    but instead 2 single quotes ('') like so

    declare startdate datetime, enddate datetime,
    sql varchar(255)

    select startdate = '06/01/2003'
    select enddate = '06/03/2003'

    select sql = 'select top 10 * from eis_arsdata where chrg_date between
    ''' + startdate + ''' AND ''' + enddate + ''''

    execute sql


    also I prefer to use set as opposed to SELECT as SELECT makes an assumption
    to cross the query engine for a recordset where SET just makes the variable
    the value.

    like so

    declare startdate datetime, enddate datetime,
    sql varchar(255)


    SET startdate = '06/01/2003'
    SET enddate = '06/03/2003'

    SET sql = 'select top 10 * from eis_arsdata where chrg_date between
    ''' + startdate + ''' AND ''' + enddate + ''''

    execute sql

    will have the same effect. Furthermore I suggest so you get a true data
    compare do a cast inside your string or you will get incorrect results do to
    the way a text date value is pd and implicit conversion handles.

    So finally

    declare startdate datetime, enddate datetime,
    sql varchar(255)


    SET startdate = '06/01/2003'
    SET enddate = '06/03/2003'


    SET sql = 'select top 10 * from eis_arsdata where chrg_date between
    cast(''' + startdate + ''' as datetime) AND cast(''' + enddate + ''' as
    datetime)'

    execute sql




    "Shamim Shamim" <com> wrote in message
    news:%phx.gbl... 


    James Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. PHP DateTime Formats
    By osalazar in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 2
    Last Post: July 4th, 03:05 PM
  2. Last MS-SQL Record by datetime
    By DFR in forum Macromedia ColdFusion
    Replies: 2
    Last Post: May 9th, 04:26 PM
  3. DateTime problem
    By FeyBaby in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 2nd, 07:29 AM
  4. [PHP] datetime
    By Larry Li in forum PHP Development
    Replies: 1
    Last Post: August 22nd, 08:44 AM
  5. datetime changes?
    By Travis Pupkin in forum ASP
    Replies: 0
    Last Post: July 18th, 07:21 AM

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