• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

TSQL - what to pass in for a datetime param?

If I manually execute a stored procedure in SSMS, how do I pass in a value for a datetime parameter?

manually execute stored proc
0
Tom Knowlton
Asked:
Tom Knowlton
  • 5
  • 3
  • 2
  • +2
4 Solutions
 
Brian CroweDatabase AdministratorCommented:
You can just put it in quotes like a string.  There are several different formats that SQL can convert implicitly.

yyyymmdd is the universal format and works no matter what your regional settings are.

try something like '20150506' for today's date
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
As the Experts said above you can just pass as string since you respect the date format.
Another option is to use GETDATE(), assuming this is for test purposes. Like for example GETDATE()-1 for yesterday date and GETDATE()-7 for last week, so you don't need to think in formats.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Tom KnowltonWeb developerAuthor Commented:
Does this help?

sql statement
0
 
Scott PletcherSenior DBACommented:
That error is not begin caused by the param/input values, it's occurring later in the proc.  Param errors have "Line 0", but this error shows line 36.

Btw, get rid of the N' in the param values, it's never required for parameters, SQL will automatically convert the string to Unicode if it's required.
0
 
Tom KnowltonWeb developerAuthor Commented:
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

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<very wild guess>

>      AND (@SearchDateExpired IS NULL OR em.AgreementEndDate = @SearchDateExpired + '%' )
So .. what's the purpose of the + '%' here?  For starters, looks like it's taking a datetime parameter and concatenating the string '%' to it, which is throwing the error.

That, and if we're doing LIKE comparisons on date values, we're playing with fire..
0
 
Tom KnowltonWeb developerAuthor Commented:
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>
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yep.  Thanks for the grade.  Good luck with your project.  -Jim
0
 
Tom KnowltonWeb developerAuthor Commented:
Thanks again, Jim!  Yep!
0
 
Scott PletcherSenior DBACommented:
You're welcome (not that you thanked me at all, but you're welcome anyway).
0
 
Tom KnowltonWeb developerAuthor Commented:
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.  : )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now