How about dateAdd(d, 1, CONVERT(DATETIME, YEAR(getDate()) + '-' + Month(getDate()) + '-' + Day(getDate())))
Because you are creating the date manually it automatically goes to midnight Steve -----Original Message----- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of barry.b Sent: Tuesday, 15 April 2008 8:56 AM To: cfaussie Subject: [cfaussie] SQL datetime functions question (SQLServer) for a report I'm needing to query on records before midnight of today (ie tonight) (SQLServer dateTime field, values stored as date time down to seconds) I can cheat and have this CF code in my WHERE clause dateadd("d", 1, LSParseDateTime(LSDateFormat(now(),"dd/mm/yyyy"))) but to be honest it's a hack (converting now() to a string which gives me 00.00.00 time and then converting back to a dateTime object then add a day to get to midnight tonight). can anyone suggest an SQL way of doing the same, perhaps using variations on getDate()? I can think of something nasty and convoluted with lots of datePart() and DateAdd() SQL functions to calculate and add/subtract hours/minutes/seconds from now but nothing clean thanks b --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---