Don't need the "Left" in the DATENAME part.

SELECT DATENAME(weekday, getdate()) as DAY,

convert(char(20), getdate(),9)+ ' ' +

Right(convert(varchar, getdate(),9),2) as [Date/Time]

DAY                            Date/Time              

------------------------------ -----------------------

Tuesday                        May 31 2005  3:56:23 PM

This seems to work:

SELECT DATENAME(weekday, LEFT (getdate(), 12)) as DAY,

convert(char(20), getdate(),9)+ ' ' + Right(convert(varchar, getdate(),9),2) as [Date/Time]

DAY                            Date/Time              

------------------------------ -----------------------

Tuesday                        May 31 2005  3:40:03 PM

Add this last part to get am/pm:

SELECT DATENAME(weekday, LEFT (getdate(), 12)) AS Day, LEFT (getdate(), 12) AS

Date, convert(varchar, getdate(),8) + Right(convert(varchar, getdate(),9),2) as [time]

Edit: But I guess that...

This will get you the hh:mm:ss but not am/pm:

SELECT DATENAME(weekday, LEFT (getdate(), 12)) AS Day, LEFT (getdate(), 12) AS

Date, convert(varchar, getdate(),8) AS [time]

In cases where the time portion really did not matter I have used datetime with a constraint to ensure all of the time values were zero.  That way I retain...

I don't have any experience with custom types for dates but have in one instance been using datetime to store "just time" and its been working fine.

I have to ask...

I found this in the Database Developer's Companion (books NOT online):

If a subquery introduced with ALL and a comparison operator does not return any values, all rows in outer...

This should give you a hint:

select Right('00' + cast(month(getdate()) as varchar),2)

----

05

For lots of good information on working with sql server dates you can check out these two articles:

http://www.karaszi.com/SQLServer/info_datetime.asp

http://www.sql-server-performance.com/fk_datetime.asp

The alias "a" referenced in the WHERE clause is not valid as there is no table with that alias.  "a' is only an alias within the definition of the derived...

Bob,

By placing "@maxPunchID int" before the AS you created an input parameter to the stored procedure and as you discovered, were required to pass in a value for that...

Display of all amounts where cents = .00

insert into #temp values (14.00)

select * from #temp

Where cast(amt as int) - amt = 0

amt    

-------

14.00

(1 row(s) affected)

Edit:...

Are you trying to return all rows where the cents part is .00 or are your trying to convert the display of all amount so that they show .00?

I think this may be what your looking for:

create table #temp (amt decimal(5,2))

insert into #temp (amt) values (12.34)

select cast(cast(amt as int)as decimal (5,2) )

from #temp

-------

12.00

Your welcome.   The other way would be to use a correlated sub query.

Select ID, PathwayID

From YourTable  as A

Where ID = (Select min(ID)

                  From YourTable

                  Where PathwayId = A.PathwayId)

Order...