sql server date format

how to get date in this format: 12/31/9999 11:59:59 PM

i am trying it like this, but not finding any proper format solution.

select convert(varchar(100), getdate(), 103)

edited:

SELECT DISTINCT U.FirstName,U.LastName, U.UserId,    
dbo.GetCity(ES.UserID) as City, dbo.GetState(ES.UserID) AS State,    
dbo.GetSchool(ES.UserID)开发者_JAVA技巧 as School, R.RoleName as RoleName    
FROM [3rdi_EventParticipants] as EP    
Inner Join [3rdi_EventSignup] as ES on EP.SignUpID=ES.SignUpID    
Inner Join Users U on ES.UserID= U.UserId
Inner Join [UserRoles] UR ON U.UserId=UR.UserId
Inner Join [Roles] R on UR.RoleID= R.RoleID        
WHERE EP.PlaceStatus in(4, 8, 3)and EP.EventID=@ItemID and UR.RoleID NOT IN(0, 1, 2, 45, 27) 
and UR.ExpiryDate>(select CONVERT(varchar(10),getdate(),101) + ' ' + CONVERT(varchar(10),getdate(),108)+ ' ' +  right(convert(varchar(30), getdate(), 9), 2))

The format you're asking for isn't a supported one.

You could probably construct it from two calls to CONVERT and a case expression:

select
   CONVERT(varchar(10),getdate(),101) + ' ' +
   CONVERT(varchar(10),getdate(),108) + ' ' +
   CASE WHEN DATEPART(hour,getdate()) >= 12 THEN 'PM' ELSE 'AM' END

(Or, by preference, leave formatting of datetime values to whatever is calling the SQL Server)

If you want to perform a meaningful date comparison, you want both sides to be datetimes, not strings (Or if you're going to use strings, use a format such as 121, where a string comparison is meaningful).

Why is UR.ExpiryDate a varchar, and not a datetime column?

Assuming your regional settings can cope with an automatic conversion of the format you've specified to a datetime value, you'd be better writing the final line of your query as:

and CONVERT(datetime,UR.ExpiryDate)>getdate()