Select date converstion - 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 2008 Forums

Transact-SQL (2008)

Select date converstion

Author

Topic

zain2
Starting Member

16 Posts

Posted - 2011-03-10 : 11:15:45

Hi everyone,

I am just trying to convert Nvarchar date from column2 and column3
Column2 got DOB in DD/MM/YYYY but stored as Nvarchar and Column3 got RDate in DD/MM/YY and stored as Nvarchar and some of the records got empty values in column2 and column3

code below is not working as it suppose to

select	[COLUMN 0] as Sname,
		[COLUMN 1] as Fname,
		(SELECT
			CASE [column 2]
				WHEN Null THEN ''
				ELSE convert(date, [column 2], 103)
			END) as DOB,
                Convert(date, [column 3], 3) as ReDate	from TempData

The first Convert Function fill empty columns to 1900/01/01
convert(date, [column 2], 103) as DOB:

The second conversation omitted loads of records

I am not sure how to show Null against empty columns...

I am not sure how to write nested queries or using case when....

Any help....

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 11:28:41

Could you supply some sample data for columns 2 and 3 and what your expected output is?
Jim

Everyday I learn something that somebody else already knew

zain2
Starting Member

16 Posts

Posted - 2011-03-10 : 11:38:15

Here is the revised code but still the same issue...

select	[COLUMN 0] as Sname,
		[COLUMN 1] as Fname,
		(SELECT
			CASE [column 2]
				WHEN Null THEN ''
				ELSE convert(date, [column 2], 103)
			END) as DOB,
                Convert(date, [column 3], 3) as ReDate	from TempData

Data
Sname, Fname, DOB, Redate
Scott J 31/01/1950 15/01/10
Scott R 01/01/1945 15/01/09
Scott K 15/03/1955 15/01/10
Scott M 31/01/1975 15/01/11
Scott L 31/01/1950 15/01/11

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 11:41:46

You're getting closer. I really mean it,though. Supply some sample data for columns 2 and 3 and what you want the output to look like or you're on your own. Unless someone else here feels like playing guessing games.
Jim

Everyday I learn something that somebody else already knew

zain2
Starting Member

16 Posts

Posted - 2011-03-10 : 11:51:54

Data as requested....

Column0,Column1, Column2, Column3
Scott J 31/01/1950 15/01/10
Scott R 01/01/1945 15/01/09
Scott K 15/03/1955
Scott M 15/01/11
Scott L 31/01/1950 15/01/11

The output should be as follows:

Sname, Fname, Dob, Rdate
Scott J 1950/01/31 2010/01/15

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 12:09:20

The first part is easy, the redate not so much. Will those dates in column 3 always be 21st century?
DECLARE @table TABLE (Column0 varchar(20),Column1 char(1),Column2 varchar(10),column3 varchar(8))
INSERT INTO @Table
SELECT 'Scott','J','31/01/1950','15/01/10' UNION
SELECT 'Scott','R','01/01/1945','15/01/09' UNION
SELECT 'Scott','K','15/03/1955','15/01/10' UNION
SELECT 'Scott','M','31/01/1975','15/01/11' UNION
SELECT 'Scott','L','31/01/1950','15/01/11'

SELECT Column0 as sName
,Column1 as fName
,convert(varchar(10),convert(datetime,column2),103) as DOB
FROM @Table

Jim

notice how I provided you with the data to work with?

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-10 : 13:41:45

Any chance you can fix your schema to use proper data types?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2011-03-10 : 15:47:12

zain2, If you use a CASE statement then the output will not all be dates as the '' is not a correct date format (thus the 1900/1/1)
May I suggest using CAST([column 2] AS DATE)

zain2
Starting Member

16 Posts

Posted - 2011-03-10 : 18:09:13

Thanks for the response, I forget to mention some of column2,3 are empty fields, what i am trying to do is to select this data and normalized before inserting into a new table.

SELECT 'Scott','J','31/01/1950','15/01/10' UNION
SELECT 'Scott','R','01/01/1945','15/01/09' UNION
SELECT 'Scott','K','','15/01/10' UNION
SELECT 'Scott','M','31/01/1975','' UNION
SELECT 'Scott','L','31/01/1950','15/01/11'

Offcourse, the empty column2,3 should carry forward into the new table as Null or just empty columns... Can we store Null value within a date field....

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2011-03-11 : 09:26:57

Yes you can store a NULL in a DATE column, if it is defined to allow them.

CREATE TABLE mytest (myDate DATE NULL); -- yes
CREATE TABLE mytest (myDate DATE NOT NULL); -- no

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources