November 16, 2016 at 1:07 pm
Hello ,
In my table view I have set a filter = getdate()
Is it possible to convert it directly in table view to short date?
thank you
November 16, 2016 at 1:19 pm
Presuming by "short date" you mean yyyy-mm-dd format, you could do this:
CONVERT(date, getdate())
You could also further tune that with the various date style options.
But, it's possible if this is in a view, you might take a performance hit with the convert.
November 16, 2016 at 5:28 pm
If you have a datetime column and you want to filter for everything collected on a specific date, you could just compare the datetime column to the whole day. Here's an example of what I mean using today and your_date_column.
AND your_datetime_column >= DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))
AND your_datetime_column < DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0)
This way, the appropriate datetime values are calculated once and you won't have to convert every value in the table before you do the comparison. If you have (or will someday have) a substantial number of rows, you'll really start to suffer by converting the column. Further, if there's a nonclustered index on that column, using a convert will ensure it isn't used. You'll be way ahead of the game if you use the calculated start and end of the day against the values in the table.
Lynn Pettis has a bunch of useful date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/ if you're interested in date math.
November 16, 2016 at 9:06 pm
peter478 (11/16/2016)
Hello ,In my table view I have set a filter = getdate()
Is it possible to convert it directly in table view to short date?
thank you
Is there a reason you posted this in the Microsoft Access forum? The responses deal with dates the way SQL Server handles them. Access does things a bit differently, so if you are working with Access, and the fact that you mention filters suggest you may be, then you need to use the Access Date() function. I should note that if you are using an Access ADP solution, which is not supported in current versions, then the SQL date functions are appropiate. Can you clarify you environment please?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 5, 2016 at 1:49 am
WendellB (11/16/2016)
Is there a reason you posted this in the Microsoft Access forum? The responses deal with dates the way SQL Server handles them. Access does things a bit differently, so if you are working with Access, and the fact that you mention filters suggest you may be, then you need to use the Access Date() function. I should note that if you are using an Access ADP solution, which is not supported in current versions, then the SQL date functions are appropiate. Can you clarify you environment please?
Hello, sorry so late, yes I use Access ADP solution and connection with SQL2008 Express.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply