Web Analytics Made Easy -
StatCounter ASp,SQL Query - CodingForum

Announcement

Collapse
No announcement yet.

ASp,SQL Query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ASp,SQL Query

    hie all, i need some help on SQL query. below are soome of my date time question in SQL.

    i got a set of data which consist of datecreated.

    1. i wan to query out a set of records which the datecreated date is within that week. let said, next week from sunday to saturday is 5th April until 11 april 2009. i need to query out the records which within next week date.

    2. in my table(MS SQL 2005), the datecreated value is store like 3/31/2009 2:17:39 PM. how can i query out the record which is today date?

    hope can get from u all soon..tq

  • #2
    2. in my table(MS SQL 2005), the datecreated value is store like 3/31/2009 2:17:39 PM
    No, it's not stored that way. Look here:
    http://msdn.microsoft.com/en-us/libr...7(SQL.80).aspx

    I quote from that:
    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers.
    So do *NOT* confuse how values appear to human beings with how the values are stored in the database! Such confusion (and assumptions) inevitably lead to problems down the road.

    Now... Having been as pedantic as my name...

    Good questions.

    Let's do number 2 first:
    2. in my table(MS SQL 2005), the datecreated value is store like 3/31/2009 2:17:39 PM. how can i query out the record which is today date?
    Since this is an ASP forum, let's use an ASP answer:
    Code:
    SQL = "SELECT * FROM yourtable " _
        & " WHERE datecreated >= '" & Date() & "' AND datecreated < '" & Date()+1 & "' " _
        & " ORDER BY datecreated"
    Why that works:

    The WHERE clause will end up looking like
    WHERE datecreated >= '4/3/2009' AND datecreated < '4/4/2009'
    When you give a date-only value (such as '4/3/2009') the DB *assumes* that the time portion is midnight. That is,
    '4/3/2009' is the same as '4/3/2009 0:00:00'
    So that query is saying "get all records on or after midnight today and BEFORE midnight tomorrow". In other words, get all records for today.

    Now, you *CAN* do this all in SQL Server code. It's just ugly as pig snot.
    Code:
    SQL = "SELECT * FROM yourtable " _
        & " WHERE CONVERT(varchar(8),datecreated,112) = CONVERT(varchar(8),getDate(),112) " _
        & " ORDER BY datecreated"
    If you really care, look here to see why that works:
    http://msdn.microsoft.com/en-us/libr...4(SQL.80).aspx

    But we're going to ignore CONVERT and answer your question 1 the same way as question 2:
    1. i wan to query out a set of records which the datecreated date is within that week. let said, next week from sunday to saturday is 5th April until 11 april 2009. i need to query out the records which within next week date.
    Again, in ASP and VBScript:
    Code:
    <%
    sundayThisWeek = Date() - Weekday(Date()) + 1
    sundayNextWeek = sundayThisWeek + 7
    sundayAfterNext = sundayNextWeek + 7
    
    SQL = "SELECT * FROM yourtable " _
        & " WHERE datecreated >= '" & sundayNextWeek & "' AND datecreated < '" & sundayAfterNext & "' " _
        & " ORDER BY datecreated"
    ...
    How that works:
    Today is April 3, 2009. That's the value of Date().
    Today is Friday. So Weekday(Date()) is 6. (The days are numbered from 1=Sunday to 7=Saturday.)
    So April 3, 2009 minus 6 days is March 28, 2009. Saturday.
    So add 1 to that date to get March 29,2009, Sunday. The Sunday prior to today's date. Try it for any date. It always works.

    So we add 7 days to that to get *next* Sunday: April 5, 2009.
    And then another 7 days after that to get April 12, 2009.

    And we put those two dates into the SQL query and end up with
    Code:
    SELECT * FROM yourtable 
    WHERE datecreated >= '4/5/2009' AND datecreated < '4/12/2009'
    ORDER BY datecreated
    Again, by using the day one AFTER the ending day we want but using <, we ensure that we get all times on 4/11/2009.

    Okay? Make sense?

    Again, we *could* do this all in T-SQL code. It's just much easier to see and understand if you mix VBScript with the SQL.
    Be yourself. No one else is as qualified.

    Comment

    Working...
    X