TSQL - what to pass in for a datetime param?

No clue.  If your question is 'How to I pass ANY date paramenter, then type something like '2015-05-06 08:00:00' for today 8am, and hit Ok.

If your question is something bigger than that, close out of that screen, then in the left treeview side of SSMS, find your server and navigate to Databases > Your Database > Programmability > Stored Procedures > the sp name, then do a right-click > Script Stored Procedure as > Alter To > New Query Editor Window.  Then when this SP is opened in a new query window, copy-paste it into this question.

Does this help?

Here is the "ALTER" statement for the stored proc above:

/****** Object:  StoredProcedure [UNAdmin].[unsp_EmployerInfoListViewSelect]    Script Date: 5/6/2015 9:31:30 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [UNAdmin].[unsp_EmployerInfoListViewSelect]

(
	@OrderByColumn nvarchar(100),
	@OrderByType nvarchar(10),
	@FirstRow int,
	@NumberOfRows int,
	@SearchName nvarchar(255) = NULL,
	@UseAdvancedSearch bit,
	@SearchLicenseNumber nvarchar(50) = NULL,
	@SearchDateIssued datetime = 0,
	@SearchDateExpired datetime = 0,
	@SearchEmployerStatus nvarchar(50) = NULL,
	@SearchPrincipal nvarchar(50) = NULL,
	@SearchAddress nvarchar(50) = NULL,
	@SearchCity nvarchar(50) = NULL,
	@SearchStateID int,
	@SearchZip nvarchar(50),
	@TotalRows int OUTPUT
)
AS

	DECLARE @t table(
		  ID int
		, EmployerName nvarchar(50)
		, AccountNumber nvarchar(10)
		, SubAccountNumber nvarchar(10)
		, EmployerStatusID int
		, EmployerStatus nvarchar(50)
		, AgreementCode nvarchar(50)
		, SupplementalAgreementCode nvarchar(50)
		, ContractLicense nvarchar(50)
		, AgreementLicense nvarchar(50))
		
	INSERT INTO @t
	SELECT em.ID, em.Name, em.AccountNumber, em.SubAccountNumber, em.EmployerStatusID, emst.Name, em.AgreementCode, em.SupplementalAgreementCode, em.ContractLicense, em.AgreementLicense
	FROM UNAdmin.Employers em
	LEFT JOIN UNAdmin.EmployerStatus emst ON em.EmployerStatusID = emst.ID
	WHERE (@UseAdvancedSearch = 0 
	AND 
	(@SearchName IS NULL OR em.Name LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR em.AccountNumber LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR em.SubAccountNumber LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR emst.Name LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR em.AgreementCode LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR em.SupplementalAgreementCode LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR em.ContractLicense LIKE '%' + @SearchName + '%' )
	OR (@SearchName IS NULL OR em.AgreementLicense LIKE '%' + @SearchName + '%' ))
	OR 
	(@UseAdvancedSearch = 1
	AND (@SearchName IS NULL OR em.Name LIKE '%' + @SearchName + '%' )
	AND (@SearchLicenseNumber IS NULL OR em.AgreementLicense LIKE '%' + @SearchLicenseNumber + '%' )
	AND (@SearchDateIssued IS NULL OR em.AgreementEffectiveDate = @SearchDateIssued)
	AND (@SearchDateExpired IS NULL OR em.AgreementEndDate = @SearchDateExpired + '%' )
	AND (@SearchEmployerStatus IS NULL OR emst.Name LIKE '%' + @SearchEmployerStatus + '%' )
	AND (@SearchPrincipal IS NULL OR em.AgreementSigner LIKE '%' + @SearchPrincipal + '%' )
	AND (@SearchAddress IS NULL OR em.Address1 LIKE '%' + @SearchAddress + '%' )
	AND (@SearchCity IS NULL OR em.City LIKE '%' + @SearchCity + '%' )	
	AND (@SearchStateID IS NULL OR em.StateID = @SearchStateID )	
	AND (@SearchZip IS NULL OR em.Zip LIKE '%' + @SearchZip + '%' )	)
	
	DECLARE @t2 table(
		  RowNum int
		, ID int
		, EmployerName nvarchar(50)
		, AccountNumber nvarchar(10)
		, SubAccountNumber nvarchar(10)
		, EmployerStatusID int
		, EmployerStatus nvarchar(50)
		, AgreementCode nvarchar(50)
		, SupplementalAgreementCode nvarchar(50)
		, ContractLicense nvarchar(50)
		, AgreementLicense nvarchar(50))
	INSERT INTO @t2
	SELECT 
		ROW_NUMBER() OVER(ORDER BY
		CASE WHEN @OrderByColumn = 'EmployerName' AND @OrderByType = 'DESC' THEN EmployerName END DESC,
		CASE WHEN @OrderByColumn = 'EmployerName' AND @OrderByType = 'ASC' THEN EmployerName END,
		CASE WHEN @OrderByColumn = 'AccountNumber' AND @OrderByType = 'DESC' THEN AccountNumber END DESC,
		CASE WHEN @OrderByColumn = 'AccountNumber' AND @OrderByType = 'ASC'  THEN AccountNumber END,
		CASE WHEN @OrderByColumn = 'SubAccountNumber' AND @OrderByType = 'DESC' THEN SubAccountNumber END DESC,
		CASE WHEN @OrderByColumn = 'SubAccountNumber' AND @OrderByType = 'ASC'  THEN SubAccountNumber END,
		CASE WHEN @OrderByColumn = 'EmployerStatus' AND @OrderByType = 'DESC' THEN EmployerStatus END DESC,
		CASE WHEN @OrderByColumn = 'EmployerStatus' AND @OrderByType = 'ASC'  THEN EmployerStatus END,
		CASE WHEN @OrderByColumn = 'AgreementNumber' AND @OrderByType = 'DESC' THEN AgreementCode END DESC,
		CASE WHEN @OrderByColumn = 'AgreementNumber' AND @OrderByType = 'ASC'  THEN AgreementCode END,
		CASE WHEN @OrderByColumn = 'SupplementalNumber' AND @OrderByType = 'DESC' THEN SupplementalAgreementCode END DESC,
		CASE WHEN @OrderByColumn = 'SupplementalNumber' AND @OrderByType = 'ASC'  THEN SupplementalAgreementCode END,
		CASE WHEN @OrderByColumn = 'ContractLicense' AND @OrderByType = 'DESC' THEN ContractLicense END DESC,
		CASE WHEN @OrderByColumn = 'ContractLicense' AND @OrderByType = 'ASC'  THEN ContractLicense END,
		CASE WHEN @OrderByColumn = 'AgreementLicense' AND @OrderByType = 'DESC' THEN AgreementLicense END DESC,
		CASE WHEN @OrderByColumn = 'AgreementLicense' AND @OrderByType = 'ASC'  THEN AgreementLicense END),
		t.*
	FROM @t t	

	SELECT @TotalRows = COUNT(RowNum) FROM @t2
	
	SELECT TOP (@NumberOfRows) 
		  ID
		, EmployerName
		, AccountNumber
		, SubAccountNumber
		, EmployerStatusID
		, EmployerStatus
		, AgreementCode
		, SupplementalAgreementCode
		, ContractLicense
		, AgreementLicense
	FROM @t2
	WHERE RowNum > (@FirstRow - 1)
	ORDER BY RowNum


GO

Select allOpen in new window

That was it, Jim.  Thank you!


Don't you just hate those typos that you stare at and miss them anyway?

It's the problem with staring at your own code for too long.  You gloss over stuff you think is working... lol...

This also demonstrates the dangers of "copy, paste, tweak" if you only "copy, paste" and don't "tweak" all the way.  <grin>

Yep.  Thanks for the grade.  Good luck with your project.  -Jim

Thanks again, Jim!  Yep!

You're welcome (not that you thanked me at all, but you're welcome anyway).

Thank you, Scott.  And thank you Vitor and Brian as well.


I am sorry I neglected to say thanks.  In all sincerity, thanks for speaking-up, Scott.  : )