Issues with cast/convert datetime/timestamp to date

Question

I have troubles when I try to cast or convert DateTime/timestamp to date.

The source is a SQL Server and the SQL I use is CONVERT(date,[the_column]) AS [the_column] or the other SQL is CAST([the_column] AS DATE) AS [the_column].

No matter which one I use I get a wrong date - instead of 2014-11-25 the result is 2014-11-23. I've tried to change the type of the column in the DB (it was datetime I changed it to timestamp) - no difference.

Does anyone know what could be the reason and what is the solution? Thank you. P.S. This is the sql I use.

  SELECT [NUMER_ZAMOWIENIA]
      ,[NUMER_ODBIORCY]
      ,CONVERT(date,[DATA_REJESTRACJI]) AS [DATA_REJESTRACJI]
      ,CAST([TERMIN_KLIENTA] AS DATE) AS [TERMIN_KLIENTA]
      ,[INDEKS]
      ,[ILOSC_ZAMOWIONA]
      ,[ILOSC_WYSLANA]
      ,[STATUS_POZYCJI]
      ,[CENA]
      ,[CENA_SPRZEDAZY]
      ,CAST([DATA_REALIZACJI] AS DATE) AS [DATA_REALIZACJI]
      ,[ORIGINAL_NUMER]
      ,DATEPART(hour,[ZAMOWIENIA].[DATA_REJESTRACJI]) AS HOUR_REG
      ,DATEPART(minute,[ZAMOWIENIA].[DATA_REJESTRACJI]) AS MIN_REG
      ,DATEPART(hour,[ZAMOWIENIA].[TERMIN_KLIENTA]) AS HOUR_TER
      ,DATEPART(minute, [ZAMOWIENIA].[TERMIN_KLIENTA]) AS MIN_TER
      ,DATEPART(hour,[ZAMOWIENIA].[DATA_REALIZACJI]) AS HOUR_REALIZ
      ,DATEPART(minute, [ZAMOWIENIA].[DATA_REALIZACJI]) AS MIN_REALIZ
  FROM [ACCESS].[dbo].[ZAMOWIENIA]

Query results in PDI

Query results in MSSQL Server

i did a lot of task on PDI so if you share tour .ktr file with me hope i can help you — Zaynul Abadin Tuhin, Aug 12 '18 at 10:35
Sample data and desired results would help. Your use a square braces is confusing, because MySQL does not recognize those. Are you sure you are using MySQL? — Gordon Linoff, Aug 12 '18 at 11:24
Well I don't know exactly how to upload the .ktr, but if it will help I may give you the sql. It is not MySQL, I don't know why my question is corrected, it's microsoft sql. — Veronika Florova, Aug 12 '18 at 16:46
1. Please tag the database used (MySQL?, SQL Server?); 2. Assuming this is SQL Server, _do not_ use `timestamp`. This is a very badly named data type that has nothing to do with time. Lastly, do a _basic_ testing query like this: `SELECT CONVERT(date,[DATA_REJESTRACJI]) AS [DATA_REJESTRACJI], [DATA_REJESTRACJI] FROM [ACCESS].[dbo].[ZAMOWIENIA]`. — Nick.Mc, Aug 13 '18 at 00:27
Yes, it's a Microsoft SQL Server. Firstly the data was datetime type, but the results were the same, so I was just guessing what could be the problem :) I did the testing query and there is no difference in the results. — Veronika Florova, Aug 13 '18 at 05:29
@VeronikaFlorova actually from your query if the column type is datetime then there is no way to change date 2014-11-25 to 2014-11-23 — Zaynul Abadin Tuhin, Aug 13 '18 at 05:35
@VeronikaFlorova could you please create your table and insert sample data http://sqlfiddle.com/#!18/9eecb in this fiddle link and show us how your data is being changed — Zaynul Abadin Tuhin, Aug 13 '18 at 05:40
How do you "know" its the wrong date? what do you do to see the value is 11-25? was it 11-25 in some other db? or some other table? If there is a time with the date please share the entire value with us. — donPablo, Aug 13 '18 at 05:58
ZaynulAbadinTuhin, ok, I'll try, but later today :) @donPablo, it's a part of transformation in Spoon and this sql is to get the data from the source db (this is the input step), so I can see the original data and the date is 11-25, but the query in Spoon throws the wrong results. — Veronika Florova, Aug 13 '18 at 06:21
@VeronikaFlorova Please tell me more about " FROM [ACCESS].[dbo].[ZAMOWIENIA]" Is this coming from a MSACCESS database? And what is the column definition in the source table? When you talk of changing the type from datetime to timestamp, are you talking about the target table (not the source)? Again, for one row please show the entire value of, say, the source input column [DATA_REJESTRACJI]. — donPablo, Aug 13 '18 at 19:50
Originally the db was created in MSAccess, however I couldn't find a way to make Access and Spoon work together, so I migrate the db to MSSQL Server. I try to change the type in the source table (in the target table there is nothing to change, the target table's column in which date should be written is of type "date"). I forgot to mention that the target tables are in PostgreSQL (if it matters). In the definition of the column in the source table the type is "Date/Time". I will upload print screens . — Veronika Florova, Aug 14 '18 at 08:13
Instead of running the Query for Entire data or top 100, Select only one record and try to Compare as it was said already this got nothing to datatype or the Convert/Cast function. So its something with the data. — Praveen DA, Aug 14 '18 at 08:26
Compare to what? I've already compared a single record in the results of the two different queries and that's how I figured the date is wrong. What could be with the data, it's the same db, the same table, the same data, however the queries are from different places - Microsoft Managment Studio and the other is from the input step in PDI. — Veronika Florova, Aug 14 '18 at 11:30
OK now run `SELECT CONVERT(date,[DATA_REJESTRACJI]) AS C1, [DATA_REJESTRACJI] FROM [ACCESS].[dbo].[ZAMOWIENIA] WHERE ID=180655` and make absolutely sure it is the record you think it is. This has still not been established. — Nick.Mc, Aug 20 '18 at 23:03

Issues with cast/convert datetime/timestamp to date

0 Answers0