Converting date fields - 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)

Converting date fields

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-05-16 : 10:32:13

Hi Guys,

I am trying to convert a datetime in the dob field which looks like this 1979-12-06 00:00:00.000 to 1979/12/06

and another date field which looks like 2008-03-16 to 2008/03/16.

I tried using the convert function but when i used it i got the same results.


eg
,convert(datetime, DateOfBirth, 110)

Below is how I incorporated the above statement in my code


Insert into amorris.Join_BB_Results
SELECT
	customer.CardNumber
	, 'CardClass' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.cardclass
		ELSE NULL
	END 
	, 'ClassCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.classcode
		ELSE NULL
	END 
	, 'TitleName' = 
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.titlename
		ELSE NULL
	END 
	, 'FirstName' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.firstname
		ELSE NULL
	END 
	, 'InitialsName' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.initialsname
		ELSE NULL
	END 
	, 'LastName' = 
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.lastname
		ELSE NULL
	END 
	, 'AddressLine1' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline1
		ELSE NULL
	END 
	, 'AddressLine2' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline2
		ELSE NULL
	END 
	, 'AddressLine3' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline3
		ELSE NULL
	END
	, 'ZipCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.ZipCode
		ELSE NULL
	END  
	, 'RegionCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.regioncode
		ELSE NULL
	END 
	, 'Email' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.email
		ELSE NULL
	END 
	, 'Occupation' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.occupation
		ELSE NULL
	END 
	, 'Gender' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.gender
		ELSE NULL
	END
	, 'MaritalStatus' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.MaritalStatus
		ELSE NULL
	END
	, 'BirthDate' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN convert(smalldatetime, Customer.birthdate, 110)
		ELSE NULL
	END

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-05-16 : 10:52:40

quote:

Originally posted by velvettiger

Hi Guys,

I am trying to convert a datetime in the dob field which looks like this 1979-12-06 00:00:00.000 to 1979/12/06

and another date field which looks like 2008-03-16 to 2008/03/16.

I tried using the convert function but when i used it i got the same results.


eg
,convert(datetime, DateOfBirth, 110)

Below is how I incorporated the above statement in my code


Insert into amorris.Join_BB_Results
SELECT
	customer.CardNumber
	, 'CardClass' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.cardclass
		ELSE NULL
	END 
	, 'ClassCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.classcode
		ELSE NULL
	END 
	, 'TitleName' = 
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.titlename
		ELSE NULL
	END 
	, 'FirstName' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.firstname
		ELSE NULL
	END 
	, 'InitialsName' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.initialsname
		ELSE NULL
	END 
	, 'LastName' = 
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.lastname
		ELSE NULL
	END 
	, 'AddressLine1' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline1
		ELSE NULL
	END 
	, 'AddressLine2' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline2
		ELSE NULL
	END 
	, 'AddressLine3' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline3
		ELSE NULL
	END
	, 'ZipCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.ZipCode
		ELSE NULL
	END  
	, 'RegionCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.regioncode
		ELSE NULL
	END 
	, 'Email' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.email
		ELSE NULL
	END 
	, 'Occupation' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.occupation
		ELSE NULL
	END 
	, 'Gender' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.gender
		ELSE NULL
	END
	, 'MaritalStatus' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.MaritalStatus
		ELSE NULL
	END
	, 'BirthDate' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN convert(smalldatetime, Customer.birthdate, 110)
		ELSE NULL
	END

Is Customer.birthdate a datetime column? If so the following will work: convert(varchar, Customer.birthdate, 111)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-16 : 11:30:07

If possible, dates should always be stored as datetime datatype (or smalldatetime). Formatting should be done only when you select data, and preferably in your front-end application.

Read this to get to grips more with working with dates...
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-05-16 : 14:33:31

Thanks everyone for your very helpful input :)

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-05-19 : 07:26:53

Hi again everyone,

Inorder to convert the birthdate field into the format 05/02/08, i used the statement convert(varchar, Customer.birthdate, 111) but now when i try to insert the results from this query into another table I am getting an error message saying

Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

Does anyone have any ideas.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 07:37:03

quote:
Originally posted by velvettiger

Hi again everyone,

Inorder to convert the birthdate field into the format 05/02/08, i used the statement convert(varchar(length), Customer.birthdate, 111) but now when i try to insert the results from this query into another table I am getting an error message saying

Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

Does anyone have any ideas.

seems like you are trying to insert date value to numeric column which causes the error. Also make sure you specify a length value for varchar column which may also cause spurious results.

velvettiger
Posting Yak Master

115 Posts

Posted - 2008-05-19 : 08:18:40

Hi,

I changed the query from what it is now to what it was previously (without doing the version) and i am still getting the same error message mentioned above. This query was working but now it isn't working and i looked through it and everything seems fine. Can someone tell me is they can spot something that I missed. Thanks in advance


/*MUST EMPTY THE TABLE EVERYTIME*/
/*This query joins the information form the Results table and the information from
the Barbados Table and places it into the amorris.Join_BB_Results table. RUN THIS 2ND*/
Insert into amorris.Join_BB_Results
SELECT
	customer.CardNumber
	, 'CardClass' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.cardclass
		ELSE NULL
	END 
	, 'ClassCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.classcode
		ELSE NULL
	END 
	, 'TitleName' = 
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.titlename
		ELSE NULL
	END 
	, 'FirstName' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.firstname
		ELSE NULL
	END 
	, 'InitialsName' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.initialsname
		ELSE NULL
	END 
	, 'LastName' = 
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.lastname
		ELSE NULL
	END 
	, 'AddressLine1' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline1
		ELSE NULL
	END 
	, 'AddressLine2' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline2
		ELSE NULL
	END 
	, 'AddressLine3' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.addressline3
		ELSE NULL
	END
	, 'ZipCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.ZipCode
		ELSE NULL
	END  
	, 'RegionCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.regioncode
		ELSE NULL
	END 
	, 'Email' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.email
		ELSE NULL
	END 
	, 'Occupation' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.occupation
		ELSE NULL
	END 
	, 'Gender' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.gender
		ELSE NULL
	END
	, 'MaritalStatus' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.MaritalStatus
		ELSE NULL
	END
	, 'BirthDate' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN Customer.birthdate
		ELSE NULL
	END 
	, ISNULL(Customer.Age, 0) AS Age
	, 'CycleCode' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN customer.CycleCode
		ELSE NULL
	END
	, 'IsCreditCardKey' =
	CASE
		WHEN Results.MagnaNumberShort IS NOT NULL AND LTRIM(Results.MagnaNumberShort) <> '' THEN AlternateAccessKeys.IsCreditCardKey
		ELSE NULL
	END  
	
  , Results.*
  , isnull(Redemptions.VoucherCount, 0) AS TotalVouchersRedeemed
  , isnull(Redemptions.PointsRedeemed, 0) AS TotalPointsRedeemed
  , isnull(Redemptions.ValueRedeemed, 0) AS TotalValueRedeemed
  , Subsidiary.SubsidiaryName
FROM Barbados.dbo.Subsidiary AS Subsidiary, 
     Results 
	LEFT OUTER JOIN Barbados.dbo.Customer AS Customer
	ON Results.MagnaNumberShort = RIGHT(Customer.CardNumber, 14)
	LEFT OUTER JOIN Barbados.dbo.AlternateAccessKeys AS AlternateAccessKeys
	ON Results.MagnaNumberShort = RIGHT(AlternateAccessKeys.CardNumber, 14)
	LEFT OUTER JOIN
	(
		SELECT cardnumber, redemptiondate, redeemingsubsidiaryid, COUNT(DISTINCT issuenumber) VoucherCount, SUM(issuedpoints) PointsRedeemed, SUM(IssuedValue) ValueRedeemed
		FROM Barbados.dbo.Statements
		GROUP BY cardnumber, redeemingsubsidiaryid, redemptiondate 
	) AS Redemptions
	ON Results.MagnaNumberShort = RIGHT( Redemptions.CardNumber, 14)
	AND Results.AccountingDate  = Redemptions.redemptiondate
	AND Results.SubsidiaryId = Redemptions.redeemingsubsidiaryid    
WHERE Results.SubsidiaryId = Subsidiary.SubsidiaryId
ORDER BY Results.StoreId, Results.LaneNumber, Results.InvoiceNumber, Results.TransactionNumber