Transform and copy a char column to a datetime column

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

SQL Server Development (2000)

Transform and copy a char column to a datetime column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-12 : 08:30:46

Charlie writes "In a SQL 2000 table, I have a nvarchar(10) column called FileDate. Its data looks like this: 2/23/2003, 7/15/1967, etc. I want to transform this data to datetime(8) and stuff it into a column called RealDate in the same table. I've tried this (see code below), but it suffers from buffer overflow. Can you help me?

Update Recordmaps
Set FileDate = RealDate
Where RealDate = convert(nvarchar(10), FileDate, 101)

Thanks in advance for your assistance.
Regards,
Charlie"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-12 : 09:23:39

[code]
Update Recordmaps
Set
RealDate = convert(datetime,FileDate)
Where
case
-- Check if FileDate is a valid date
when isdate(FileDate) = 1
then case
-- Check if dates not the same
when RealDate <> convert(datetime,FileDate) or RealDate is null
then 1
else 0
end
else 0
end = 1 -- Perform update when case result = 1

[/code]

quote:
Originally posted by AskSQLTeam

Charlie writes "In a SQL 2000 table, I have a nvarchar(10) column called FileDate. Its data looks like this: 2/23/2003, 7/15/1967, etc. I want to transform this data to datetime(8) and stuff it into a column called RealDate in the same table. I've tried this (see code below), but it suffers from buffer overflow. Can you help me?

Update Recordmaps
Set FileDate = RealDate
Where RealDate = convert(nvarchar(10), FileDate, 101)

Thanks in advance for your assistance.
Regards,
Charlie"

Codo Ergo Sum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources