> Thanks for the quick help, I tried the datepart function
> but it doesn't
> look like it can return more the the date, month OR year.
> I need to get all
> parts or there may be some inconsistencies in the data.
> I also tried the Ben's suggestion using Concatenation.
> SELECT Month(myDate) + '/' + Day(myDate) + '/' +
> Year(myDate)
> But I get an error : Syntax error converting the varchar
> value '/' to a
> column of data type int.
Those are probably better than my suggestion, although you still have to
convert the numbers ( which I may have forgotten in my example also ) ...
SELECT CONVERT(varchar,Month(myDate)) + '/'
+ CONVERT(varchar,Day(myDate)) + '/'
+ CONVERT(varchar,Year(myDate)) AS DateAtMidnight
and if you want to sort by date in date order, just to be sure, you probably
want to then convert that string _back_ to a date, just to be sure you don't
wind up comparing a string '1/1/2002' with a string '10/1/2001' or something
like that...
SELECT CONVERT(smalldatetime,
(CONVERT(varchar,Month(myDate)) + '/'
+ CONVERT(varchar,Day(myDate)) + '/'
+ CONVERT(varchar,Year(myDate))) AS DateAtMidnight
All of this damnable conversion is the reason why I suggested a user-defined
function if you're using SQL 2000 ... write it once and then reuse it... :)
Although at that, if you're using CF 5 and you're not using a stored
procedure to perform the query, you could write a UDF like
getSQLDate(columnname,alias); that would spit out the sql string to return
the value you want also and would have similar value to the SQL Server UDF,
although SQL is preferable if available because it keeps the function with
the data just in case you want to connect some other programming language to
this db later ...
hth
Isaac Dealey
Certified Advanced ColdFusion Developer
www.turnkey.to
954-776-0046
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
