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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy