December 14, 2012 at 6:40 am
I need to convert a SQL Server DateTime2 Column to an AS400 Date.
The format of the AS400 Date is CYYMMDD.
Edit:
I found a solution:
DECLARE @MyDate DATETIME2
SET @MyDate = GETDATE()
SELECT cast(datepart(yyyy,@MyDate) - 1900 as varchar(4))
+ cast(datepart(mm,@MyDate) AS varchar(2))
+ cast(datepart(dd,@MyDate) AS varchar(2)) as MyDate
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2012 at 7:54 am
I created a simple function to convert the Date.
CREATEFUNCTIONdbo.fncConvertSQLToAS400Date
(@SQLDateDateTime2)
RETURNSINT
-------------------------------------------------------------------------------------------
AS
-------------------------------------------------------------------------------------------
BEGIN
DECLARE@rtnAS400DateINT
SET @rtnAS400Date =
(SELECT cast(datepart(yyyy,@SQLDate) - 1900 as varchar(4))
+ cast(datepart(mm,@SQLDate) AS varchar(2))
+ cast(datepart(dd,@SQLDate) AS varchar(2)) )
RETURN @rtnAS400Date
END
DECLARE @MyDate DATETIME2
SET @MyDate = GETDATE()
SELECT dbo.fncConvertSQLToAS400Date(@MyDate)
I need to add code in the event that a Date is not passed to the function.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2012 at 8:02 am
I tested the function using a Date in the 20th Century and it does not work.:hehe:
DECLARE @MyDate DATETIME2
SET @MyDate = '1999-01-01'
SELECT dbo.fncConvertSQLToAS400Date(@MyDate)
:blush:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2012 at 9:38 am
The following is a function that converts an AS400 Date Format to SQL Server.
Credit Jeff Moden.
CREATE FUNCTION [dbo].[udf_ConvertDB2toSQL](@CSC_DATE varchar(7))
RETURNS DateTime
AS
BEGIN
IF (LEN(ltrim(rtrim(@CSC_DATE))) = 6)
BEGIN
SET @CSC_DATE= '0' + @CSC_DATE
END
DECLARE @SQLDate Datetime
SET @SQLDate = Stuff(
Stuff(
Stuff(@CSC_Date, 6, 0, '-')
, 4, 0, '-')
, 1, 1, CASE LEFT(@CSC_DATE, 1) WHEN 0 THEN '19' WHEN 1 THEN '20' END
)
RETURN @SQLDate
END
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply