Billy Pang
tuxedo_man at hotmail.com
Thu Oct 2 16:44:15 CDT 2003
just some thoughts: - the two new variables (start and finish datetime) should be declared as datetime as well... - from BOL (smalldatetime): Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. - from BOL (ldatetime): Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table. >From: "Selina Iddon" <selina at easydatabases.com.au> >Reply-To: dba-sqlserver at databaseadvisors.com >To: <dba-sqlserver at databaseadvisors.com> >Subject: Re: [dba-SQLServer] DateTime Hell! >Date: Fri, 3 Oct 2003 06:16:41 +1000 > >Hi Billy >Thanks for your reply. I'm passing an int variable, it uses that to lookup >the rest of the information it requires, including the smalldatetime start >and finish and puts them in two new variables within the SP. I haven't >tried the convert 101, I'll give that a go. Also, I was using datetime, >not >smalldatetime, so I'll try that. >Thanks >Selina > >---------------------------------------------------------------------------- >---- Selina Iddon selina at easydatabases.com.au Ph: 0414 225 265 Easy Access >Databases >----- Original Message ----- >From: "Billy Pang" <tuxedo_man at hotmail.com> >To: <dba-sqlserver at databaseadvisors.com> >Sent: Friday, October 03, 2003 2:32 AM >Subject: Re: [dba-SQLServer] DateTime Hell! > > > > hmmm... not sure what the issue you are trying to describe is but I'll >give > > it a shot... > > > > 1) is the parameter you are trying to pass in a datetime variable or >text? > > 2) how are you comparing your dates? > > 3) when you pass in the date, try using the 101 format (ie. > > convert(smalldatetime, '10/02/2003',101)) > > > > Billy > > > > > > >From: "Selina Iddon" <selina at easydatabases.com.au> > > >Reply-To: dba-sqlserver at databaseadvisors.com > > >To: <dba-sqlserver at databaseadvisors.com> > > >Subject: [dba-SQLServer] DateTime Hell! > > >Date: Thu, 2 Oct 2003 22:14:00 +1000 > > > > > >Hi Everyone > > >I haven't participated in this list too much because I'm relatively new >to > > >SQL and with the help of Susan's book and reading this list have been > > >struggling through, but this one has me at my wits end. > > > > > >I am passing a parameter to a stored procedure and using this ID it > > >retrievies the datetime from a view and is assigned to a variable. I >then > > >want to assess whether the times overlaps with other datetimes in the >same > > >view. The stored procedure is converting the date format to 2003-10-03 > > >14:00:00.000 when putting it in the variable and my view has the date >time > > >formatted as 3 Oct 2003 2:00:00 PM and they won't compare to each >other, > > >even though they are both happily inside convert(datetime,XXX) >functions > > >and > > >have come from the same field in the same view. I've tried using > > >convert(datetime,XXX,13) replacing the 13 with 6, 9 and multiple other > > >combinations as described on a web site I found, but nothing works. > > > > > >Please Please help if you can. I've lost days on this project which is >due > > >Monday (in my dreams!) > > > > > >TIA > > >Selina > > > > > > > > >_______________________________________________ > > >dba-SQLServer mailing list > > >dba-SQLServer at databaseadvisors.com > > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > >http://www.databaseadvisors.com > > > > > > > _________________________________________________________________ > > MSN 8 with e-mail virus protection service: 2 months FREE* > > http://join.msn.com/?page=features/virus > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com