SmallDate comparison syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SmallDate comparison syntax

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-25 : 11:11:46

I have a sproc that uses a date parameter for retrieving records. I've tried various methods of comparison all resulting in syntax errors. I'm not sure how to get quotes, or whatever is needed in this case, around the Date. Here's my code (where ReceivedDate = smalldate):

@XSortOrder nvarchar(100), @XFromDate datetime
AS
SET NOCOUNT ON
Declare @Sql varchar(1000)
CREATE TABLE #Temp (RowNumber int IDENTITY (1, 1),ShipmentID int)
If @XSortOrder is Null Or @XSortOrder = '' Begin
Set @Sql = 'INSERT #Temp (ShipmentID) SELECT ShipmentID FROM tblShipments WHERE ReceivedDate >= #' + @XFromDate + '#'
Exec (@Sql)
End
Else Begin
Set @Sql = 'INSERT #Temp (ShipmentID) SELECT ShipmentID FROM tblShipments WHERE ReceivedDate >= ' +
CONVERT(char(8),@XFromDate,112) + ' ORDER BY ' + @XSortOrder
Exec (@Sql)
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-25 : 11:25:58

Try this:


Set @Sql = 
'INSERT #Temp (ShipmentID) '+
'SELECT ShipmentID FROM tblShipments '+
WHERE ReceivedDate >= ''' +
convert(varchar(40),@XFromDate,121) + ''''

CODO ERGO SUM

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-25 : 12:11:12

Thank you. It works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources