Convert 10 to 2010 - 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)

Convert 10 to 2010

Author

Topic

Lorna70
Starting Member

19 Posts

Posted - 2010-09-14 : 12:12:54

Hi

I have a varchar field with values I want to convert to dates. The trouble is they are in the following format:

10/03/10, 16/03/10 etc. I need to convert all these so they end in 2010 so that I can then change the field type to date. How do I do this in a SQL query?

Thanks
Lorna

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 12:22:07

If they are all like that (ending in 10) then you can do this:

DECLARE @foo TABLE (
	[fooID] INT PRIMARY KEY
	, [dateVal] CHAR(8)
	)
INSERT @foo
      SELECT 1, '10/03/10'
UNION SELECT 2, '16/03/10'
UNION SELECT 3, '12/01/49'
UNION SELECT 4, '12/01/50'

SET DATEFORMAT dmy

SELECT
	f.[fooID]
	, f.[dateVal]
	, CAST(f.[dateVal] AS [DateTime])
FROM
	@foo AS f

The Cast / Convert will flip between 19xx and 20xx around 50 (so if the year is 49 it's 2049 and if it's 50 it's 1950).

Note the SET DATEFORMAT dmy

Store the values from the CAST / CONVERT in a DATETIME field -- then you should be OK.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION