Sunday, March 11, 2012

DateTime formating.

I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;

* 8:00 AM Get to work
* Independance day

some of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time..

I've tried this...

selectEvent=(CONVERT(CHAR(8),start,8)+' '+ title)fromCalendarwhereCONVERT(CHAR(8),start,8)<>'12:00:00'

It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time?

Thanks for any help.

Mark

Perhaps something like this might work:

SELECTCASEWHEN(datevalisNOTNULLAND dateval!='')THEN REPLACE(REPLACE(RIGHT(CONVERT(varchar,dateval,100),7),'P',' P'),'A',' A')ELSE''END +' ' + titleFROM yourTableWHERE

|||

--Try somthing like this:

SELECTCASEWHENCONVERT(varchar(8),date,108)='12:00:00'THEN'*'+title --Or CONVERT(varchar(8),date,108)='00:00:00'

ELSE'*'+RIGHT(CONVERT(varchar(19),date,100), 7)+' '+ title

ENDASEvent

FROM calendarwithTitle

|||Perfect thanks alot that took care of it for me!

No comments:

Post a Comment