Post reply

sql

  • August 28, 2010 at 7:27 am

    #237260

    I'm trying to query values with a particular timestamp on SQL Server 2005, however, the simplest option won't work:

    SELECT [EntryID]

    ,[Timestamp]

    ,[Subject]

    ,[EntryText]

    FROM [couch_db].[dbo].[Article]

    WHERE [Timestamp] = 2010-28-08;

    So I tried a more complicated option following this article, and, somehow it won't work either:

    SELECT [EntryID]

    ,[Timestamp]

    ,[Subject]

    ,[EntryText]

    FROM [couch_db].[dbo].[Article]

    WHERE [Timestamp] >= 2010-28-08 AND [Timestamp] < (DATEADD(dd, 1, 2010-28-08));

    Table: Ariticle

    EntryID - PK, bigint, not null

    Timestamp (datetime, not null)

    Subject (nvarchar(40), not null)

    EntryText(nvarchar(max), null)

    Any ideas? Btw, any idea what nvarchar(max) represents in terms of size?

    😀

    Version Info

    Microsoft SQL Server Management Studio Express9.00.2047.00

    Microsoft Data Access Components (MDAC)2000.085.1132.00 (xpsp.080413-0852)

    Microsoft MSXML2.6 3.0 4.0 5.0 6.0

    Microsoft Internet Explorer8.0.6001.18702

    Microsoft .NET Framework2.0.50727.3615

    Operating System5.1.2600

  • August 28, 2010 at 7:32 am

    #1213411

    🙂

    I had a syntax error; following this article, I changed the SQL to:

    SELECT [EntryID]

    ,[Timestamp]

    ,[Subject]

    ,[EntryText]

    FROM [couch_db].[dbo].[Article]

    WHERE [Timestamp] >= 2010-28-08 AND [Timestamp] < (DATEADD(day, 1, [Timestamp]));

  • August 28, 2010 at 7:50 am

    #1213417

    Try

    WHERE [Timestamp] >= '2010-08-28 00:00:00.000' and [Timestamp] <= '2010-08-28 23:59:59.999';

    MCITP SQL Server 2005/2008 DBA/DBD

  • August 28, 2010 at 7:54 am

    #1213418

    I forgot to add, I believe the nvarchar(max) is roughly 2GB

    MCITP SQL Server 2005/2008 DBA/DBD

  • August 28, 2010 at 12:42 pm

    #1213465

    Brian O'Leary (8/28/2010)

    I forgot to add, I believe the nvarchar(max) is roughly 2GB

    Look it up in Books online where you'll find that although it contains a binary 2GB, it will only contain a binary 1GB worth of characters because of the two byte per character nature of NVARCHAR(). 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 28, 2010 at 12:50 pm

    #1213468

    Brian O'Leary (8/28/2010)

    Try

    WHERE [Timestamp] >= '2010-08-28 00:00:00.000' and [Timestamp] <= '2010-08-28 23:59:59.999';

    Hi Brian,

    That's not the recommended way to do things because 1)... 23:59:59.999 rounds UP to the next day for DATETIME datatypes be cause the resolution is 3.33 Milli-Seconds and 2) it could miss some times for the new TIME datatype which has a much more finite resolution.

    Most of the Ninja's on this site agree that you should use something similar to the following to isolate whole days in the WHERE clause... some might even call it a best practice...

    WHERE [TimeStamp] >= @StartDate

    AND [TimeStamp] < DATEADD(dd,1,[@EndDate])

    Let's all hope that [TimeStamp] is just the name of the column in this case and that the OP isn't actually trying to convert a TIMESTAMP datatype to anything.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 28, 2010 at 1:43 pm

    #1213483

    Jeff Moden (8/28/2010)

    Brian O'Leary (8/28/2010)

    Try

    WHERE [Timestamp] >= '2010-08-28 00:00:00.000' and [Timestamp] <= '2010-08-28 23:59:59.999';

    Hi Brian,

    That's not the recommended way to do things because 1)... 23:59:59.999 rounds UP to the next day for DATETIME datatypes be cause the resolution is 3.33 Milli-Seconds and 2) it could miss some times for the new TIME datatype which has a much more finite resolution.

    Most of the Ninja's on this site agree that you should use something similar to the following to isolate whole days in the WHERE clause... some might even call it a best practice...

    WHERE [TimeStamp] >= @StartDate

    AND [TimeStamp] < DATEADD(dd,1,[@EndDate])

    Let's all hope that [TimeStamp] is just the name of the column in this case and that the OP isn't actually trying to convert a TIMESTAMP datatype to anything.

    Thanks Jeff I didn't know that!

    With regards to the NVARCHAR(MAX) only allowing for 1GB characters, this isn't really true (comparing it to VARCHAR(MAX) seems like comparing apples to oranges) it still holds 2GB it just uses 2 bytes per character.

    MCITP SQL Server 2005/2008 DBA/DBD

  • August 28, 2010 at 2:13 pm

    #1213490

    Thanks for the feedback, Brian. And you and I have said virtually the same thing now on NVARCHAR(MAX)... 2GB max storage requirement (same as VARCHAR(MAX)), 1GB max displayable characters. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 30, 2010 at 11:00 am

    #1213840

    Jeff Moden (8/28/2010)

    Brian O'Leary (8/28/2010)

    Try

    ....

    Most of the Ninja's on this site agree that you should use something similar to the following to isolate whole days in the WHERE clause... some might even call it a best practice...

    WHERE [TimeStamp] >= @StartDate

    AND [TimeStamp] < DATEADD(dd,1,[@EndDate])

    SELECT [Timestamp], [Subject], [EntryText] FROM [Article] WHERE [Timestamp] >= '8/25/2010 12:00:00 AM' AND [Timestamp] < (DATEADD(day, 1, [Timestamp]));

  • August 30, 2010 at 12:41 pm

    #1213898

    the retro-question is : What's the datatype of your column [timestamp] ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • August 30, 2010 at 12:55 pm

    #1213904

    Thanks I sorted it out

    C#

    ...

    System.String.Concat("SELECT [Timestamp], [Subject], [EntryText] FROM [Article] WHERE [Timestamp] = '" +

    calArticles.SelectedDate.ToString() + "';");

    which translates to:

    SELECT [Timestamp], [Subject], [EntryText] FROM [Article] WHERE [Timestamp] = '8/30/2010 12:00:00 AM';

    It wasn't the SQL that was wrong I guess, in the first place 🙂

  • August 31, 2010 at 1:08 am

    #1214060

    Jon-538504 (8/30/2010)

    Thanks I sorted it out

    C#

    ...

    System.String.Concat("SELECT [Timestamp], [Subject], [EntryText] FROM [Article] WHERE [Timestamp] = '" +

    calArticles.SelectedDate.ToString() + "';");

    which translates to:

    SELECT [Timestamp], [Subject], [EntryText] FROM [Article] WHERE [Timestamp] = '8/30/2010 12:00:00 AM';

    It wasn't the SQL that was wrong I guess, in the first place 🙂

    Keep in mind your .ToString may be sensitive to CLIENT side settings for date representation !!

    Also have a look at the articles regarding SQL injection ! e.g. http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • September 4, 2010 at 4:25 pm

    #1216578

    I thought I should update due to an error posted.

    The assertion that the datetime data type rounds up by 3.33 ms is incorrect. After some research on this datatype I found the following in BOL:

    Rounding of datetime Fractional Second Precision

    datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

    User-specified value System stored value

    01/01/98 23:59:59.999 1998-01-02 00:00:00.000

    01/01/98 23:59:59.995

    01/01/98 23:59:59.996

    01/01/98 23:59:59.997

    01/01/98 23:59:59.998 1998-01-01 23:59:59.997

    01/01/98 23:59:59.992

    01/01/98 23:59:59.993

    01/01/98 23:59:59.994 1998-01-01 23:59:59.993

    01/01/98 23:59:59.990

    01/01/98 23:59:59.991

    This definition was found in the documentation of all versions of SQL Server going back to SQL 2000.

    MCITP SQL Server 2005/2008 DBA/DBD

  • September 4, 2010 at 5:39 pm

    #1216591

    Brian O'Leary (9/4/2010)

    I thought I should update due to an error posted.

    The assertion that the datetime data type rounds up by 3.33 ms is incorrect. After some research on this datatype I found the following in BOL:

    Rounding of datetime Fractional Second Precision

    datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

    User-specified value System stored value

    01/01/98 23:59:59.999 1998-01-02 00:00:00.000

    01/01/98 23:59:59.995

    01/01/98 23:59:59.996

    01/01/98 23:59:59.997

    01/01/98 23:59:59.998 1998-01-01 23:59:59.997

    01/01/98 23:59:59.992

    01/01/98 23:59:59.993

    01/01/98 23:59:59.994 1998-01-01 23:59:59.993

    01/01/98 23:59:59.990

    01/01/98 23:59:59.991

    This definition was found in the documentation of all versions of SQL Server going back to SQL 2000.

    No one asserted that DATETIME {always} rounds up by 3.33 seconds. If you go back and look at my post you'll see that I said that 23:59:59.999 will round up because DATETIME has a RESOLUTION of 3.33 seconds. Nothing in there about always rounding up.

    Thanks for the posting though. And, I suppose I could have been a wee bit more clear.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

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