Problem to convert Varchar datatype to datetime2

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

General SQL Server Forums

New to SQL Server Programming

Problem to convert Varchar datatype to datetime2

Author

Topic

Arunraj
Starting Member

18 Posts

Posted - 2014-10-14 : 05:59:30

Need to fetch the date from parent table to chile table
this is the scriptwhich they give to me.
case When @AccountingDate IS NULL THEN NULL ELSE CONVERT (varchar,@AccountingDate , 101) END,
Case When @InventoryDate IS NULL THEN NULL ELSE CONVERT (varchar,@InventoryDate,101) END,
Case When @StatusDate IS NULL THEN NULL ELSE CONVERT (varchar,@StatusDate,101) END,
Case When @LastInstallmentDate IS NULL THEN NULL ELSE CONVERT (varchar,@LastInstallmentDate,101) END,
Case When @RetailFirstPayDate IS NULL THEN NULL ELSE CONVERT (varchar,@RetailFirstPayDate,101) END ,
Case When @LeaseFirstPayDate IS NULL THEN NULL ELSE CONVERT (varchar,@LeaseFirstPayDate,101) END ,
Case When @DayToFirstPayment IS NULL THEN NULL ELSE CONVERT (varchar,@DayToFirstPayment,101) END ,
Case When @EntryDate IS NULL THEN NULL ELSE CONVERT (varchar,@EntryDate,101) END ,
Case When @DealBookDate IS NULL THEN NULL ELSE CONVERT (varchar,@DealBookDate,101) END ,
Case When @RealBookDate IS NULL THEN NULL ELSE CONVERT (varchar,@RealBookDate,101) END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-14 : 08:26:11

quote:
Originally posted by Arunraj

Need to fetch the date from parent table to chile table
this is the scriptwhich they give to me.
case When @AccountingDate IS NULL THEN NULL ELSE CONVERT (varchar,@AccountingDate , 101) END,
Case When @InventoryDate IS NULL THEN NULL ELSE CONVERT (varchar,@InventoryDate,101) END,
Case When @StatusDate IS NULL THEN NULL ELSE CONVERT (varchar,@StatusDate,101) END,
Case When @LastInstallmentDate IS NULL THEN NULL ELSE CONVERT (varchar,@LastInstallmentDate,101) END,
Case When @RetailFirstPayDate IS NULL THEN NULL ELSE CONVERT (varchar,@RetailFirstPayDate,101) END ,
Case When @LeaseFirstPayDate IS NULL THEN NULL ELSE CONVERT (varchar,@LeaseFirstPayDate,101) END ,
Case When @DayToFirstPayment IS NULL THEN NULL ELSE CONVERT (varchar,@DayToFirstPayment,101) END ,
Case When @EntryDate IS NULL THEN NULL ELSE CONVERT (varchar,@EntryDate,101) END ,
Case When @DealBookDate IS NULL THEN NULL ELSE CONVERT (varchar,@DealBookDate,101) END ,
Case When @RealBookDate IS NULL THEN NULL ELSE CONVERT (varchar,@RealBookDate,101) END

It is hard to tell what the problem is because we don't have any context. Is this part of a query? The code you have posted on its own is not valid and will generate a parsing error. When you run it, what is the exact text of the error message?

Some sample input data, the expected output and the code you are currently using are required to offer any useful suggestions.

Arunraj
Starting Member

18 Posts

Posted - 2014-10-15 : 08:58:26

AccountingDate,InventoryDate,StatusDate,LastInstallmentDate,RetailFirstPayDate,LeaseFirstPayDate,DayToFirstPayment,EntryDate,DealBookDate,RealBookDate

These are the column names available in parent table and we gave datatype as VARCHAR(50) NULL to all the columns.
Same columns will be available in child table also.But we gave datatype for child table as DATETIME2(7) NULL for all the columns.

Using stored procedure i am calling through the above script from parent table to child table.
But in output first column alone coming as NULL to all the rows .Rest all the columns are fine.
Please suggest to change the script to get same output from the parent table to child table by using same datatype as i mentioned above.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 11:36:46

Please post the ENTIRE script (not just the case clause) and some sample input data and what you want for output data.

Arunraj
Starting Member

18 Posts

Posted - 2014-10-16 : 01:13:53

Its the big script and l tried to load but its not loading what to do.

Arunraj
Starting Member

18 Posts

Posted - 2014-10-16 : 01:14:43

BEGIN TRY

INSERT INTO SALES_DATE
(
SalesID ,
AccountingDate ,
InventoryDate ,
StatusDate ,
LastInstallmentDate ,
RetailFirstPayDate ,
LeaseFirstPayDate ,
DayToFirstPayment ,
EntryDate ,
DealBookDate ,
RealBookDate
)
VALUES
(
@SalesID,
(Case when ISDATE(AccountingDate )=1 Then AccountingDate Else NULL End ,
Case when ISDATE(InventoryDate )=1 Then InventoryDate Else NULL End ,
Case when ISDATE(StatusDate)=1 Then StatusDate Else NULL End ,
Case when ISDATE(LastInstallmentDate)=1 Then LastInstallmentDate Else NULL End ,
Case when ISDATE(RetailFirstPayDate)=1 Then RetailFirstPayDate Else NULL End ,
Case when ISDATE(LeaseFirstPayDate)=1 Then LeaseFirstPayDate Else NULL End ,
Case when ISDATE(DayToFirstPayment)=1 Then DayToFirstPayment Else NULL End ,
Case when ISDATE(EntryDate)=1 Then EntryDate Else NULL End ,
Case when ISDATE(DealBookDate)=1 Then DealBookDate Else NULL End ,
Case when ISDATE(RealBookDate)=1 Then RealBookDate Else NULL End

) from FLATFILE_SALES
);
END TRY
BEGIN CATCH
SELECT
@errornumber = ERROR_NUMBER()
,@errorseverity = ERROR_SEVERITY()
,@errorstate = ERROR_STATE()
,@errorprocedure = ERROR_PROCEDURE()
,@errorline = ERROR_LINE()
,@errormessage = ERROR_MESSAGE()

Arunraj
Starting Member

18 Posts

Posted - 2014-10-16 : 01:15:04

This the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources