Need help with datetime comparision

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with datetime comparision

Author

Topic

beaner
Starting Member

2 Posts

Posted - 2004-05-13 : 12:59:19

What I am trying to accomplish is to compare the Date() that I have stored in a temporary variable called tempDate with the Date portion of a datetime field in a sql database. Below is a snippet of what I have currently in my .asp page which is not working. My goal is to stop a user from adding a duplicate shift record for the same date.

Thanks in advance for help you might be able to provide.

==================================

tempDate = Date()

set shiftCreate = Server.CreateObject("adodb.recordset")
shiftCreate.Open "select count(*) as mycount from tbShiftTurnNotes where shift='"&strShift&"' and convert(char(10), dateCreated, 101)='"&tempDate&"'",Application("DSN"),3,3

if shiftCreate("mycount")=0 then ... blah, blah, blah

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-13 : 13:02:54

The best way to strip the time off of a datetime typed column/variable is :

dateadd(dd,datediff(dd,0,<datetime typed thing>),0)

In your case, it looks like you could use datediff(dd,tempDate,datecreated) = 0 ...

Jay White
{0}

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-13 : 14:46:22

I do this by using three key fields in the table. user,Mdate,shift

Set the default of the user to the user ID(I use the NT ID) (suser_sname(suser_sid()))

Set the Mdate to CONVERT(varchar(10), getdate(), 101)

and let the user enter the shift

and Wala no dupes and you do not have to compare.

Watch 3rd shift!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111

this is a little trick to handle 2nd and 3rd.

Set @Run_date = CASE WHEN (CONVERT(varchar(50), getdate(), 108) BETWEEN '00:00:01' AND '04:00:00') And @Shift = '2' THEN
CONVERT(varchar(10), getdate() - 1, 101)
WHEN (CONVERT(varchar(50), getdate(), 108) BETWEEN '16:00:00' AND '23:59:59') And @Shift = '3'
THEN CONVERT(varchar(10), getdate() + 1, 101)
ELSE CONVERT(varchar(10), getdate(), 101) END

Jim
Users <> Logic

beaner
Starting Member

2 Posts

Posted - 2004-05-14 : 11:05:08

Thanks for the responses. I ended up creating a function to format Date() function as a string and that worked for me. It took me awhile as I needed the month to be in the format "mm" and I came up with this:

Function fDateFormat ( strTempDate )
DIM strMonth 'Month
DIM strDay 'Day
DIM strYear 'Year

strMonth = "00" + Cstr(Month(strTempDate))
strMonth = Cstr(Right(strMonth,2))
strDay = "00" + Cstr(Day(strTempDate))
strDay = Cstr(Right(strDay,2))
strYear = Cstr(Year(strTempDate))
fDateFormat = strMonth + "/" + Cstr(strDay) + "/" + Cstr(strYear)
End Function

It is probaby a roundabout way to do it, but it works! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources