| Author |
Topic |
|
nockam
Starting Member
3 Posts |
 Posted - 2005-12-29 : 20:08:14
|
| I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-29 : 20:53:26
|
Use this in your where clause:where -- greater than or equal to beginning of today startime >= dateadd(dd,datediff(dd,0,getdate()),0) and -- less than beginning of tomorrow startime < dateadd(dd,datediff(dd,0,getdate())+1,0) CODO ERGO SUM |
 |
|
|
nockam
Starting Member
3 Posts |
Posted - 2005-12-29 : 21:13:10
|
| Thank you for your quick help. When I add all of that in i get a sytax error. If i add in just the first line "startime >= dateadd(dd,datediff(dd,0,getdate()),0)" it takes it, if i add in just the last line "startime < dateadd(dd,datediff(dd,0,getdate())+1,0)" it tells me "The Dateadd function requires 3 arguments. statement(s) could not be prepared." |
|
|
|
nockam
Starting Member
3 Posts |
Posted - 2005-12-29 : 21:49:21
|
| I played with it a little more and got it figured out! If I replace "startime < dateadd(dd,datediff(dd,0,getdate())+1,0)"With"startime < dateadd(dd,datediff(dd,0,getdate()),+1)" It works!Michael, Thank you very much for your help tonight, without you I would have been stuck at work for several more hours. Thanks, Garrett |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
tahseenm
Yak Posting Veteran
64 Posts |
Posted - 2006-12-06 : 11:37:51
|
| I create a front end MS Access table and back end sql table. On my sql table I select the datatype as date/time and on the default value I put this one (dateadd(day,datediff(day,0,getdate()),0). When I input the data in to a table and refresh its showing 12/06/2006. But when I checked in access its showing the date data as 2006-12-06 00: but I want to show 12/06/2006. Can you tell what I have to do to come out like that. Thanks and appreciate your comments.moetahsen |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-06 : 11:43:48
|
quote:
Originally posted by tahseenm
I create a front end MS Access table and back end sql table. On my sql table I select the datatype as date/time and on the default value I put this one (dateadd(day,datediff(day,0,getdate()),0). When I input the data in to a table and refresh its showing 12/06/2006. But when I checked in access its showing the date data as 2006-12-06 00: but I want to show 12/06/2006. Can you tell what I have to do to come out like that. Thanks and appreciate your comments.moetahsen
Put this as a new topic in MS-Access forum..don't piggyback on existing topic!Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-06 : 23:34:45
|
| http://sqlteam.com/forums/topic.asp?TOPIC_ID=75985MadhivananFailing to plan is Planning to fail |
|
|
|
|
Problem with current date for date & time field