September 28, 2004 at 4:45 am
I am an SQL novice, but I did manage to create an SQL query with Enterprise Manager.
I need to output results from a table where the date value in the [tmestamp] matches the current systems date.
the [tmestamp] value is nvarchar and appears in the table with a format dd/mm/yyy hh:mm:ss
This query works if I enter the current date in the query:
select [stats].[type], [stats].[machine], [stats].[tmestamp], [stats].[svalue]
from [stats]
where convert(nvarchar(10), [stats].[tmestamp]) = '28/09/2004'
This one works, but I can only get it to compare the day value:
select [stats].[type], [stats].[machine], [stats].[tmestamp], [stats].[svalue]
from [stats]
where convert(nvarchar(2), [stats].[tmestamp]) = DATEPART(day, GETDATE())
How do i get a compare of the full dd/mm/yyy value with the system date? I will be setting this up to automatically execute each day at 11:00.
September 28, 2004 at 5:57 am
Hi,
I hope you mean that the date is dd/mm/yyyy?
If that is the case, this should do the trick:
select [stats].[type], [stats].[machine], [stats].[tmestamp], [stats].[svalue]
from [stats]
where convert(nvarchar(10), [stats].[tmestamp])
= convert(nvarchar(10), getdate(), 103) -- Uses style 103
More information about convert and the usage of styles can be found in BOL.
September 28, 2004 at 7:09 am
EdK,
I think that IF you want to compare records to the current system date/time you probably will NEVER get a record.
WHY? Glad you asked... The system date/time will basically be NOW and you would have to be very lucky to get records that were entered in NOW.
You may want to get more information about the range of data you are looking for. IF they still want NOW at least you can go back to them and state # of records will be low or 0.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 29, 2004 at 2:36 am
Thanks olavho, that worked
AJ Ahrens, point taken, I only needed data selected for the current system dd/mm/yyyy.
Didn't know about BOL, downloaded and installed now and its a great resource.
September 29, 2004 at 7:14 am
To strip the "time component" off a date/time value use:
DATEADD(d,DATEDIFF(d,0,[DateVar]),0)
It's much faster than CONVERT. What it actually does is return the date/time value with the time set to 12:00:00 AM.
Your WHERE clause to return all records where ([tmestamp] = any time on today's date )becomes:
WHERE DATEADD(d,DATEDIF(d,0,[stats].[tmestamp]),0)
= DATEADD(d,DATEDIF(d,0, getdate()),0)
We do this so much that we added a separate column [DateOnly] to our database which the trigger sets using that function. The [DateOnly] column is indexed and that really speeds up our queries.
BTW - If you choose to use the CONVERT method, it's our shop policy to ALWAYS specify the style in every CONVERT that is used for comparisons. If you don't and someone changes the default style for your server to yyyy/mm/dd... , all your CONVERT statements suddenly start coming out different and things stop working.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply