DateTime Problem
-
Hi I have this code:
Declare @v_update_sql varchar(max) Declare @d_Date datetime Declare @v_target_table_name varchar(max) Set @v_target_table_name = 'IM_DOC_TRANSFORM' Set @d_Date = '2009-03-02 12:46:00.000' Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'+@d_Date+')' Exec(@v_update_sql)The error that is arising is that of cannot convert to datetime from string.the variable @d_Date is a valid date but still it cannot convert it. That statement works went not in a string variable but in this format it doesn't. Can someone help me please? Thanks -
Hi I have this code:
Declare @v_update_sql varchar(max) Declare @d_Date datetime Declare @v_target_table_name varchar(max) Set @v_target_table_name = 'IM_DOC_TRANSFORM' Set @d_Date = '2009-03-02 12:46:00.000' Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'+@d_Date+')' Exec(@v_update_sql)The error that is arising is that of cannot convert to datetime from string.the variable @d_Date is a valid date but still it cannot convert it. That statement works went not in a string variable but in this format it doesn't. Can someone help me please? ThanksHi jonhbt, Try this, you seem to be missing some qoutation makers on the CONVERT in your dynamic SQL which are necessary for a STRING to be concatenated. Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'''+@d_Date+''')' I hope this helps, Kevin
-
Hi jonhbt, Try this, you seem to be missing some qoutation makers on the CONVERT in your dynamic SQL which are necessary for a STRING to be concatenated. Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'''+@d_Date+''')' I hope this helps, Kevin
-
Hi Kevin, Thanks for your help but the same happened. This is the error that I am Having. Conversion failed when converting datetime from character string.
OK maybe this will help instead... Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = CAST('''||@d_Date||''' AS DATETIME)' If it does not work, do a "SELECT @v_update_sql" immediately after the "SET @v_update_sql..." statement and see what the dynamic SQL looks like with the date string included. You might see an obvious syntax error then perhaps. Cheers, Kevin
-
OK maybe this will help instead... Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = CAST('''||@d_Date||''' AS DATETIME)' If it does not work, do a "SELECT @v_update_sql" immediately after the "SET @v_update_sql..." statement and see what the dynamic SQL looks like with the date string included. You might see an obvious syntax error then perhaps. Cheers, Kevin
-
Hi I have this code:
Declare @v_update_sql varchar(max) Declare @d_Date datetime Declare @v_target_table_name varchar(max) Set @v_target_table_name = 'IM_DOC_TRANSFORM' Set @d_Date = '2009-03-02 12:46:00.000' Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + 'Where Last_Documented_Date_And_Time = Convert(datetime,'+@d_Date+')' Exec(@v_update_sql)The error that is arising is that of cannot convert to datetime from string.the variable @d_Date is a valid date but still it cannot convert it. That statement works went not in a string variable but in this format it doesn't. Can someone help me please? ThanksHi again, One thing I just noticed is you need a SPACE before the "WHERE clause" in the SQL example code you posted above. At the moment it dynamic SQL command looks like this. Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORMWhere Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME) Try adding a space as follows. I have also replaced the "||" with "+" if you would prefer this method of concatenation. declare @v_update_sql varchar(1000) declare @d_date char(23) select @d_date = '2009-03-02 12:46:00.000' declare @sql varchar(1000) declare @v_target_table_name varchar(100) Set @v_target_table_name = 'IM_DOC_TRANSFORM' Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + ' Where Last_Documented_Date_And_Time = CAST('''+@d_Date+''' AS DATETIME)' select @v_update_sql execute (@v_update_sql) The dynamic SQL should then look like this... Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORM Where Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME) See if that works for you. Cheers, Kevin
-
Hi again, One thing I just noticed is you need a SPACE before the "WHERE clause" in the SQL example code you posted above. At the moment it dynamic SQL command looks like this. Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORMWhere Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME) Try adding a space as follows. I have also replaced the "||" with "+" if you would prefer this method of concatenation. declare @v_update_sql varchar(1000) declare @d_date char(23) select @d_date = '2009-03-02 12:46:00.000' declare @sql varchar(1000) declare @v_target_table_name varchar(100) Set @v_target_table_name = 'IM_DOC_TRANSFORM' Set @v_update_sql = 'Select * From HRS_STAGING.dbo.'+ @v_target_table_name + ' Where Last_Documented_Date_And_Time = CAST('''+@d_Date+''' AS DATETIME)' select @v_update_sql execute (@v_update_sql) The dynamic SQL should then look like this... Select * From HRS_STAGING.dbo.IM_DOC_TRANSFORM Where Last_Documented_Date_And_Time = CAST('2009-03-02 12:46:00.000' AS DATETIME) See if that works for you. Cheers, Kevin