Dynamic SQL dates - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-19 : 08:50:08

Michael writes "I have a Stored Procedure with the following Dynamic SQL statement:

Select @SQL = 'Insert Into ' + @TBLName + '(Transaction_ID, client_mastid, Bureau_ID, InternalID, Trans_Bankstmt_Ref, Trans_Type, Service_Type, ActionDate, Trans_Value, Homing_AccNo, Homing_AccType, Homing_BranchCode, Homing_AccName, AccLink_ID, ACB_TapeID, Trans_Status, Tape_SeqNum, Client_Gen_Number)'
Select @SQL = @SQL + 'Select Transaction_ID, client_mastid, Bureau_ID, InternalID, Trans_Bankstmt_Ref, Trans_Type, Service_Type, ActionDate, Trans_Value, Homing_AccNo, Homing_AccType, Homing_BranchCode, Homing_AccName, AccLink_ID, ACB_TapeID, Trans_Status, Tape_SeqNum, Client_Gen_Number From Transactions Where Service_Type = 02 And Trans_Status = 0 And Schedule_ID = 0 And ActionDate <= ' + @Start5Day

the @Stard5Day is a varchar

The result is as follows:
Insert Into Mikki(Transaction_ID, client_mastid, Bureau_ID, InternalID, Trans_Bankstmt_Ref, Trans_Type, Service_Type, ActionDate, Trans_Value, Homing_AccNo, Homing_AccType, Homing_BranchCode, Homing_AccName, AccLink_ID, ACB_TapeID, Trans_Status, Tape_SeqNum, Client_Gen_Number)Select Transaction_ID, client_mastid, Bureau_ID, InternalID, Trans_Bankstmt_Ref, Trans_Type, Service_Type, ActionDate, Trans_Value, Homing_AccNo, Homing_AccType, Homing_BranchCode, Homing_AccName, AccLink_ID, ACB_TapeID, Trans_Status, Tape_SeqNum, Client_Gen_Number From Transactions Where Service_Type = 02 And Trans_Status = 0 And Schedule_ID = 0 And ActionDate <= 2002-10-01

This does not copy any records unless I manually cange the date to '2002-10-01'"

izaltsman
A custom title

1139 Posts

Posted - 2002-04-19 : 11:21:07

Well, since @Start5Day is a varchar, you have to delimit it by single quotes. So I guess this would work:

Select @SQL = 'Insert Into ' + @TBLName + '(Transaction_ID, client_mastid, Bureau_ID, InternalID, Trans_Bankstmt_Ref, Trans_Type, Service_Type, ActionDate, Trans_Value, Homing_AccNo, Homing_AccType, Homing_BranchCode, Homing_AccName, AccLink_ID, ACB_TapeID, Trans_Status, Tape_SeqNum, Client_Gen_Number)'
Select @SQL = @SQL + 'Select Transaction_ID, client_mastid, Bureau_ID, InternalID, Trans_Bankstmt_Ref, Trans_Type, Service_Type, ActionDate, Trans_Value, Homing_AccNo, Homing_AccType, Homing_BranchCode, Homing_AccName, AccLink_ID, ACB_TapeID, Trans_Status, Tape_SeqNum, Client_Gen_Number From Transactions Where Service_Type = 02 And Trans_Status = 0 And Schedule_ID = 0 And ActionDate <= ''' + @Start5Day + ''''

If you want to be even safer, you can wrap a CONVERT function around it to make sure it gets converted using the correct style.

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources