DateTime Conversion - 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

SQL Server Development (2000)

DateTime Conversion

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-04-13 : 09:00:36

Guys,
I have scenario dealing with datetime fields
There are 2 columns date and time which are both varchar(50) fields

Date has data in the format 04/13/2005
Time has data in the format 11:34

I have created new column named date_time with datetime datatype

Now I want to update this column with concatenation of date and time fields but it errors out for me.
However, it works when I try to update date_time column with date only.

Any workarounds for this scenario would help

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-13 : 09:44:19

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([date] varchar(50), [time] varchar(50), [datetime] datetime)
GO

INSERT INTO myTable99([date],[time])
SELECT '04/13/2005','11:34' UNION ALL
SELECT '04/13/2005','25:00' UNION ALL
SELECT '04/33/2005','11:34' UNION ALL
SELECT 'Brett','Kaiser'
GO

-- Show all Bad datetime values

SELECT *
FROM myTable99
WHERE ISDATE([date])=0
OR ISDATE([time])=0

-- Show all Good datetime values

SELECT *
FROM myTable99
WHERE ISDATE([date])=1
AND ISDATE([time])=1

--Update the Column

UPDATE myTable99
SET [datetime] = CONVERT(datetime,[date]+' '+[time])
FROM myTable99
WHERE ISDATE([date])=1
AND ISDATE([time])=1

-- Have a look

SELECT * FROM myTable99
GO

--Cleanup this mess

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources