February 22, 2016 at 11:52 am
Thanks in advance for any help here... I have some dates I've moved from Access into SQL Server and I need to update all of them from a updating dates from a mm/dd/yy to yyyy-mm-dd 00:00:00.000 format. Can this be done with an update script in T-SQL? The field is unfortunately in varchar instead of datetime because of the Access conversion and I'm not sure I can change it but I can create a new temp field to handle that part... any ideas there would help too.
Thanks!
February 22, 2016 at 12:00 pm
briancampbellmcad (2/22/2016)
Thanks in advance for any help here... I have some dates I've moved from Access into SQL Server and I need to update all of them from a updating dates from a mm/dd/yy to yyyy-mm-dd 00:00:00.000 format. Can this be done with an update script in T-SQL? The field is unfortunately in varchar instead of datetime because of the Access conversion and I'm not sure I can change it but I can create a new temp field to handle that part... any ideas there would help too.Thanks!
Just create the temp field as datetime. Then the update can convert varchar to datetime.
February 22, 2016 at 12:52 pm
Something like this?:
INSERT INTO [CPCE_DB].[dbo].[Orders].[OrderDateAlt]
SELECT Cast[AdminDateID] AS Datetime
FROM [CPCE_DB].[dbo].[Orders]
February 22, 2016 at 1:10 pm
briancampbellmcad (2/22/2016)
Something like this?:INSERT INTO [CPCE_DB].[dbo].[Orders].[OrderDateAlt]
SELECT Cast[AdminDateID] AS Datetime
FROM [CPCE_DB].[dbo].[Orders]
Take a look at this:
ALTER TABLE [CPCE_DB].[dbo].[Orders] ADD [OrderDateAlt] (DATETIME);
UPDATE TABLE [CPCE_DB].[dbo].[Orders]
SET [OrderDateAlt] = CAST([AdminDateID] AS Datetime);
February 22, 2016 at 1:34 pm
getting an error: Msg 173, Level 15, State 1, Line 1
The definition for column 'OrderDateAlt' must include a data type.
ALTER TABLE [CPCE_DB].[dbo].[Orders] ADD [OrderDateAlt] (DATETIME);
or ALTER TABLE [CPCE_DB].[dbo].[Orders] ADD [OrderDateAlt] (DATETIME) NULL;
February 22, 2016 at 2:18 pm
UPDATE [CPCE_DB].[dbo].[Orders]
SET [OrderDateAlt] = CAST([AdminDateID] AS Datetime);
gives: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
February 22, 2016 at 3:45 pm
briancampbellmcad (2/22/2016)
UPDATE [CPCE_DB].[dbo].[Orders]SET [OrderDateAlt] = CAST([AdminDateID] AS Datetime);
gives: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
This likely means you have a value in your string column that won't convert to a datetime. You need to find them and decide what to do with them before doing the UPDATE statement. You can use the ISDATE function to help you find them, but it isn't always 100%.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply