Problem with date

Results 1 to 5 of 5

Thread: Problem with date

  1. #1
    Shoua Guest

    Default Problem with date

    Like to query data from a MS SQL 7.0 table based on the first day of the month and time. I have a table that is getting data written into every 15 minutes so the table getting big fast. Currently, I use windows scheduler to execute this statement to get what I wanted: SELECT TimeStampColumn FROM plant_table1 WHERE TimeStampColumn &#062;= getdate()-0.0100 AND &#060;= getdate()<BR><BR>The scheduler is set to run first day of the month at 12:00AM, because data from current month minus data from previous month = total energy usage. Is there a better way? I wish I can query anytime within the month and still get the data from the time that my windows scheduler gave me. Any helps? Thanks. <BR>

  2. #2
    clarification Guest

    Default RE: Problem with date

    you could change the getdate() calls to variables then you would select everything from table where timestamp is &#062;= startdate and timestamp &#060;=enddate, you can do this both in asp code and within a stored proceedure, i&#039;m not sure i understand your problem though. so if you wanted last month then startdate = &#039;4/1/2001 00:00:00&#039; and endate = &#039;4/30/2001 23:23:59&#039;, and you can choose any other range you want as well.

  3. #3
    Shoua Guest

    Default RE: Problem with date

    Thanks for a response. Basically, I use MS Query to pull data from MS SQL Server 7.0 to MS Excel spread sheet. I am only interested in getting a snapshot of the data on every first day of the month at 12am. Once the data are in MS Excel, I can save the data to different column and do the math there. The data from last month are saved in here in Excel when I queried the data last month. My data(records) in MS SQL table are just continuous reading(every 15 minutes) of our plant electric energy meters throughout the plant. I don&#039;t need a startdate or endate, but my query should recognize that I am interested in May 1 if I run the query on May 3 or 5, so on. If I run the query on June 11 or 13, it should give me June 1. Hope this makes some sense to you. Thanks

  4. #4
    ahhhh... Guest

    Default RE: Problem with date

    this is a little more complex since you always want to start from the first you could hardcode it...but that&#039;s not good practice either...enjoy<BR>timestamp &#062;= dateadd(day,-(day(getdate())-1),getdate())<BR>and timestamp &#060;= getdate()

  5. #5
    Shoua Guest

    Default RE: Thanks

    Thank you very much to an unknown SQL guru. It works for me. You guys certainly know your stuffs. I guess I cannot buy you a cup of coffee so please just accept my sincere "thank you" for a job well done. Bye now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts