Casting one datetime as date converts other datetime fields
-
I have a table with two DateTime fields. When I select them and cast one of them as date, it returns them both as date. For example:
select
orderdate
from tableAwould return results in datetime format. But this:
select
orderdate
,cast(paiddate as date) as paiddatereturns both column as date only. Why would it do that?
- robert.wiglesworth - Tuesday, October 16, 2018 8:58 AM
Can you provide some code which allows us to reproduce this? My code behaves as expected:
DROP TABLE IF EXISTS #SomeDates;CREATE TABLE #SomeDates
(
Date1 DATETIME
, Date2 DATETIME
);INSERT #SomeDates
(
Date1
, Date2
)
VALUES
(
GETDATE(), GETDATE()
);SELECT
sd.Date1
, sd.Date2
, CAST(sd.Date1 AS DATE)
, CAST(sd.Date2 AS DATE)
FROM #SomeDates sd;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I copy and pasted your code and I get the same results. If I comment out the lines in the select statement that does the casting, the results for Date1 and Date2 show date and time. If I run your code as it is, then all for results show only date, not date and time. WTH?
-
Screenshot please? And where are you running this from?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
-
What client is that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is TOAD for SQL Server. I just tried the same code in Management Studio and it worked as expected. So something with TOAD? But the real problem is that it is happening during an SSIS package that is run on a scheduled SQL job. So now it might become a question for the SSIS forum. So weird...
-
If it's happening in SSIS, then that's a problem. I can see a client tool weirdness, but SSIS is another matter.
Assuming SSIS is inserting into some table with date and datetime columns, have you checked to see whether Toad and SSMS show different results from that table without the CAST?
eg, does this work as expected?DROP TABLE IF EXISTS #SomeDates;CREATE TABLE #SomeDates
(
Date1 DATETIME
, Date2 DATE
);INSERT #SomeDates
(
Date1
, Date2
)
VALUES
(
GETDATE(), GETDATE()
);SELECT Date1, Date2 FROM #SomeDates
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
That code works as expected in SSMS but again, not in TOAD. I am going to test some things in SSIS to see what I can figure out.
-
Then consider the possibility that the problem is not in SSIS, but in Toad's display of the data that SSIS has loaded.
Double-check in SSMS before you go digging into the SSIS packagesGail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - robert.wiglesworth - Tuesday, October 16, 2018 9:55 AM
Heh.... we used to say that they called it "Toad" because it croaks on a regular basis and will give you warts. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OK, so I figured this out. It was in SSIS. I looked at the metadata for the process that was pulling this data and it had dbdate as the data type for that column. It must have carried that from copy and pasting in the query or something. Either way, I edited the data type for that field to dbtimestamp and now everything is working as desired.
Thanks for helping me get through this!
- robert.wiglesworth - Wednesday, October 17, 2018 9:16 AM
This does not square up with your earlier post:
I copy and pasted your code and I get the same results. If I comment out the lines in the select statement that does the casting, the results for Date1 and Date2 show date and time. If I run your code as it is, then all for results show only date, not date and time. WTH?
SSIS has nothing to do with this. Please explain.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Those problems were only with the TOAD interface. Apparently it has a problems with dates or something when doing select statements. When I ran that code in SSMS it worked fine. But I couldn't figure out why SSIS was not bringing over the time when doing the insert. That's were the metadata settings in SSIS came in.
- robert.wiglesworth - Wednesday, October 17, 2018 10:43 AM
Bit of a coincidence, but OK!
In the north east of England, toad is pronounced 'turd'. I can now see that they are on to something.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply