| Author |
Topic |
|
phyxe
Starting Member
13 Posts |
 Posted - 2012-05-07 : 23:48:04
|
Hi.. I'm in desperate need of help with my server here wherein i have a table that records all of transaction made by all users using our client app, the table has a column labeled txntime where it is in datetime2 format i would like to have a query wherein it is going to search for records basing on the current system date and I want a result that would fall within the 24 hour range of the system date. As of the moment i use this kind of query select COUNT (*) as [TOTAL Transaction for the day] from TxnLog where txnDate between '2012-05-07' and '2012-05-07 11:59:59 PM' as you can see i need to change the date every time i need to generate a report for a certain day, I want to make it so that i can get results from the query basing on the current system date since I would be using it in a one of the reports im making. thank you in advance for any help  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-08 : 12:15:34
|
| [code]select COUNT (*) as [TOTAL Transaction for the day] from TxnLog where txnDate >= dateadd(dd,datediff(dd,0.getdate()),-1)and txnDate < dateadd(dd,datediff(dd,0.getdate()),0)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
phyxe
Starting Member
13 Posts |
Posted - 2012-05-09 : 03:49:58
|
thanks for you reply to my question but i tried this one out I encountered some errors on the query, are there some things that i still need to change on it specially on the dateadd(dd,datediff(dd,0.getdate()),-1) since that's where error is by the way here's the error that i got quote:
Incorrect syntax near 'getdate'.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-09 : 16:16:52
|
quote:
Originally posted by phyxe
thanks for you reply to my question but i tried this one out I encountered some errors on the query, are there some things that i still need to change on it specially on the dateadd(dd,datediff(dd,0.getdate()),-1) since that's where error is by the way here's the error that i got quote:
Incorrect syntax near 'getdate'.
are you using sql server?getdate is t-sql function so if you're using any other rdbms it wont work. Also this is ms sql server forum so if you're using any other dbms please post in relevant forums------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
phyxe
Starting Member
13 Posts |
Posted - 2012-05-10 : 01:18:57
|
| well im using ms sql 2008 r2, when i typed in the query that you provided i got that error |
|
|
|
matty
Posting Yak Master
161 Posts |
Posted - 2012-05-10 : 01:31:13
|
Its a typo quote:
Originally posted by visakh16
quote:
Originally posted by phyxe
thanks for you reply to my question but i tried this one out I encountered some errors on the query, are there some things that i still need to change on it specially on the dateadd(dd,datediff(dd,0,getdate()),-1) since that's where error is by the way here's the error that i got quote:
Incorrect syntax near 'getdate'.
are you using sql server?getdate is t-sql function so if you're using any other rdbms it wont work. Also this is ms sql server forum so if you're using any other dbms please post in relevant forums------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
phyxe
Starting Member
13 Posts |
Posted - 2012-05-10 : 01:59:44
|
| oh i got it now. the first code has periods on it not commas, so slow of me to check that one any ways i got the query working fine now... thank you for all the help :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-10 : 12:08:07
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Query using system date on a transaction log table