 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ColdFusion / Advanced Techniques / November 2006Date Time insert problem
Heeeeelp please! Dates. And Times. Raise your hand if you hate them. I have a problem with dates/times and I was hoping that someone can help. I am developing a vacancy list. On the list vacancies page, I want to display all vacancies that closingDate >= #now()# On the add a vacancy I am asking the user to insert a date (closingDate) for a vacancy. However, when I insert the date, it defaults to different result depending on the function I use. I want to enter a date in the closingDate that mimics the now() function which is inserted correctly. Here is the closest i have got to insert at least something that looks promissing. <cfset closingDate = "#form.closingDate#" /> (form.closingDate value is '27/11/2006') <cfset closingDate = #createDateTime(year(closingDate), month(closingDate), day(closingDate), 23,59,59)#/> closingDate = <cfqueryparam value="#createODBCDateTime(closingDate)#" cfsqltype="cf_sql_date" /> The DB field defaults to '27/11/2006 12:00:00' (the field is set to smalldatetime and i've tried it with datetime as well on MsSQL 2k) I have had major problems with dates as well. I think because were using the european format. First, you don't need the createodbcddatetime in the cfparam, you have already done this above it. I have tried setting the locale and all other thinks with no luck, so this is what I do now. I make sure that the date can only be entered into the form field with a particular format (dd/mm/yyyy) I check this both at the client and the server. I then pull the form field apart in the createdatetime function, note I also do this if the time values are passed in the form as well. Ken <cfset closingDate = "#form.closingDate#" /> (form.closingDate value is '27/11/2006') <cfset closingDay = ListGetAt(closingDate, 1, "/")> <cfset closingMonth = ListGetAt(closingDate, 2, "/")> <cfset closingYear = ListGetAt(closingDate, 3, "/")> <cfset closingDate = #createDateTime(closingYear, closingMonth, closingDay, 23,59,59)#/> <!-- value is a date time, so need to use sql type of timestamp ---> <!-- if it is just a date then use sql type of date ---> closingDate = <cfqueryparam value="#closingDate#" cfsqltype="CF_SQL_TIMESTAMP"> Thanks for the prompt reply Ken. However it does not work for me. Maybe I haven't explained it well. When I code it as you have suggested, the isert statement will not insert the hh:mm:ss - and it will round the date up - so for example if I have 28/11/2006 23:59:59 it will round it up to 29/11/2006 in the field (note - no time). I do not want this - I want this to be exactly as I have specified it above. I have noticed that the now() function is entered in such format only if you do not use the cfqueryparam. It will not work in the same way with the above variable because it is a string (don't judge for having tried it - I am getting desperate). Well I've found the problem. I've been inserting a date and time which actually gets rounded up/down to the nearest 30 seconds. For example: [b]28/11/2006 23:59:59[/b] would be rounded up to [b]29/11/2006 00:00:00[/b] but the [b]00:00:00[/b] would not show because frankly they are zero's (i.e. trailing zero?s). I?ve noticed that if I put [b]28/11/2006 23:59:29[/b] it will round it down to [b]28/11/2006 23:59:00[/b] This way I am losing only one minute, which for my application, is not that drastic. It seems like CF does not cater for precise times. I may be wrong but this is what I?ve found in my environment and if anyone knows better then please let me know. Right! I poked a bit further and I think the problem is not with CF - it is with MsSQL. It is MsSQL that rounds numbers up/down. http://support.microsoft.com/kb/135861/en-us This seems to happen even with milliseconds - i.e. 999 is rounded up by 1 which has the 'reverse domino effect' on the date/time. Not that I need it, but for curiosity sake, how does CF handle milliseconds? Or does it? I have never had this problem, but I have not needed to use milliseconds either. But the MS article indicates that this should only happen if the millisecond is 999. Thus it should not round down as you have indicated. What version of MS SQl Server and level of patch have you installed ? After looking at the CF function for date/time it would appear that CF only goes to seconds. Ken I am running MsSQL 2k running on winXP Prof with Service Pack 2 on my local machine. The same is installed on the server running win2k Server. I don't think for the sake of the application that I am developing I need milliseconds, but I was getting curious. If I lose a minute for the closingDate then so be it. Maybe I will get back to it when I have a bit more time. On another note, is there a way of getting the date given the week of the year? I want to find out the dates Monday to Friday given the week and the year. Just a point to note. If you installed winXP sp2 after you installed sql server 2k sp 4 then you will need to install sql server 2k sp 4 again. [Q]On another note, is there a way of getting the date given the week of the year?[/Q] Yes, but off hand I could not tell you how. Ken |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.