Formatting a String into a certain Datetime Format

[bjr149]

Im reading in a date from a table as a varchar (50) an example of the string is.

Feb 7 2006 7:00PM

I want to update the same field in the table with a new formatted date. One like 02/07/2006. Heres my code. The supplier formats correctly filling with zeros. But nothing happens with my date. This is very important i need to bring the correct for back to the mainframe. Thanks for the help. Below is the current code.

DECLARE @invoicedate varchar(50)
DECLARE @suppliernum varchar(50)
DECLARE @supplierformat varchar(50)
DECLARE @time datetime
DECLARE @record bigint

DECLARE cursorx CURSOR for
SELECT InvoiceDate,SupplierID,record_id from AP_Payment_Push_Hold
open cursorx
fetch next from cursorx
into @invoicedate,@suppliernum,@record
WHILE(@@fetch_status = 0)
begin
set @time = convert(datetime,@invoicedate,110)
set @supplierformat = (select right(replicate('0', 10) + ltrim(@suppliernum), 10))

update AP_Payment_Push_Hold
set InvoiceDate = @time,
SupplierID = @supplierformat
where record_id = @record

set @time = NULL
set @invoicedate = NULL

fetch next from cursorx
into @invoicedate,@suppliernum,@record
end
close cursorx
deallocate cursorx

 

[SQLDenis]

won't work like that, a datetime field is not a string field, either make the field a varchar/char or do
convert(datetime,InvoiceDate,110) when you insert the data backinto your Mainframe


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/