[SOLVED] formatting Date

[rondog]

I am pulling a date from a mssql table with data type of 'smalldatetime'

 

it looks like this in the database: 6/10/2008, but when I run a fetch array on it and echo it out, it looks like Jun 10 2008. I applied no formatting whatsoever to it. It just comes out looking like that. Any idea why?

 

I am wanting to achieve something that looks like Tuesday, June 10th, 2008 but have had no luck yet.

 

Thanks for any help.

[DarkWater]

Make the column datetime and then select the row with DATE_FORMAT().

[rondog]

Make the column datetime and then select the row with DATE_FORMAT().

 

Will making the column datetime mess with any of the current data in the table? Their is something like 9,000 entries in there at the moment.

[DarkWater]

Make the column datetime and then select the row with DATE_FORMAT().

 

Will making the column datetime mess with any of the current data in the table? Their is something like 9,000 entries in there at the moment.

 

I strongly doubt it, but make a test table with a smalldatetime, put some entries in and try converting it. =P

[rondog]

hehe ok thank you

[rondog]

I was about to reply and say PHP was returning an undefined function for date_format..I didnt realized it was a MySQL function. Does the same go for MSSQL because thats what I am using? Any idea on how to write it? This is my current query:

 

<?php
include 'mssqlconnect.php';

$date = date("m/d/Y");
$query = mssql_query("SELECT event_date FROM DE_Events WHERE displayOnSite = 'true' AND event_date >= '$date' ORDER BY event_date");

?>

 

[DarkWater]

Oh never mind, MSSQL doesn't have a DATE_FORMAT (I don't think so, at least).  Honestly, I'd get a MySQL database.  Two reasons:

 

1) Microsoft generally sucks. 

 

2) MySQL has more features, MSSQL doesn't have limit clauses, etc.

[rondog]

Trust me I would if I could! Unfortunatly I was dragged into the middle of this project :[

 

They have a events.cfm page that is using the same table formatting it the way I need to to be so maybe I'll just try and get that file from the client. Thanks anyway!

[rondog]

Well I got it fairly quick..Here it is in Cold Fusion:

				<p>
					#DateFormat(event_date, "dddd, mmmm dd, yyyy")#<br />
					<cfif isDefined("start_hour") and start_hour gt 0>
						#start_hour#:#start_minute_text# #start_ampm# 
					</cfif>
					<cfif isDefined("end_hour") and end_hour gt 0>
						— #end_hour#:#end_minute_text# #end_ampm#
					</cfif>
				</p>

 

I dont know cold fusion so I am kind of lost..

 

I am trying this right now but its not working

<?php
$query = mssql_query("SELECT DateFormat(event_date, 'dddd, mmmm dd, yyyy') FROM DE_Events WHERE displayOnSite = 'true' AND event_date >= '$date' ORDER BY event_date");
?>

[grimmier]

 

try this

 

"SELECT Date_Format(event_date, '%W, %M %D %Y') FROM DE_Events WHERE displayOnSite = 'true' AND event_date >= '$date' ORDER BY event_date"

 

[DarkWater]

@grimmier: That's MySQL, not MSSQL. =/

[bluejay002]

i dont know much bout mssql since am using mysql... so how bout trying to format the date from PHP instead?

 

<?php
   $date = '6/10/2008'; // you may use the data from the database here instead

   $formatted_date = date('l, F jS, Y', strtotime($date)); // date format for "Tuesday, June 10th, 2008"

   echo $formatted_date; // output Tuesday, June 10th, 2008
?>

 

cheers,

 

Jay

[grimmier]

oops my bad, try this

 

 

$query = mssql_query("SELECT Format(event_date, 'dddd, mmmm dd, yyyy') FROM DE_Events WHERE displayOnSite = 'true' AND event_date >= '$date' ORDER BY event_date");

[rondog]

i dont know much bout mssql since am using mysql... so how bout trying to format the date from PHP instead?

 

<?php
   $date = '6/10/2008'; // you may use the data from the database here instead

   $formatted_date = date('l, F jS, Y', strtotime($date)); // date format for "Tuesday, June 10th, 2008"

   echo $formatted_date; // output Tuesday, June 10th, 2008
?>

 

cheers,

 

Jay

 

Hey that worked Jay. Very nice