How to get exactly date from Oracle Link DB in SQL server.
-
Hi All. I have SQL Server, link to Oracle DB. In the SQL server I have a T-SQL command select Moto.ne_code, Moto.supplier_id, Moto.ne_type_id, Moto.sta_datetime, from PMCALCULATE..[PM_TOOL].[MOTOROLA_CELL_HOURLY_KPI] Moto and I get the result. But the sta_datetime in the result is not ok it is similar to: 2016-02-03 00:00:00 00000000 How to format it to yyyy/mm/dd hh24
-
Hi All. I have SQL Server, link to Oracle DB. In the SQL server I have a T-SQL command select Moto.ne_code, Moto.supplier_id, Moto.ne_type_id, Moto.sta_datetime, from PMCALCULATE..[PM_TOOL].[MOTOROLA_CELL_HOURLY_KPI] Moto and I get the result. But the sta_datetime in the result is not ok it is similar to: 2016-02-03 00:00:00 00000000 How to format it to yyyy/mm/dd hh24
What you are getting is a DATETIME data type which is correct. If you want to display it in a particular format then user CONVERT to get the format you need. You MUST store the data in your SQL Server table as DATETIME, NEVER store DATETIME as a VARCHAR.
Never underestimate the power of human stupidity RAH
-
What you are getting is a DATETIME data type which is correct. If you want to display it in a particular format then user CONVERT to get the format you need. You MUST store the data in your SQL Server table as DATETIME, NEVER store DATETIME as a VARCHAR.
Never underestimate the power of human stupidity RAH
-
I have try select convert(datetime2(0),Moto.sta_datetime) as Sta_Date1, convert(datetime,Moto.sta_datetime) as Sta_Date2, But the result is not as i want All The result is:2016-02-03 00:00:00 I Want date only and time only How can I do this.
-
I have try select convert(datetime2(0),Moto.sta_datetime) as Sta_Date1, convert(datetime,Moto.sta_datetime) as Sta_Date2, But the result is not as i want All The result is:2016-02-03 00:00:00 I Want date only and time only How can I do this.
Where are you using the result: storing it in the database - do NOT format the data displaying it in a UI - the UI needs to do the formatting using it in a report - the report needs to do the formatting exporting it to a file - this is the ONLY scenario where you should convert the datetime to a string (varchar)
convert(varchar(20),DateValue,103)is an example.Never underestimate the power of human stupidity RAH
-
Where are you using the result: storing it in the database - do NOT format the data displaying it in a UI - the UI needs to do the formatting using it in a report - the report needs to do the formatting exporting it to a file - this is the ONLY scenario where you should convert the datetime to a string (varchar)
convert(varchar(20),DateValue,103)is an example.Never underestimate the power of human stupidity RAH
I only Want to get Yesterday data from Oracle store in the SQL database.So that why I want to get exacly date to compare with the date in the SQL Database.
-
I only Want to get Yesterday data from Oracle store in the SQL database.So that why I want to get exacly date to compare with the date in the SQL Database.
If Oracle has a DATE type (no time component) then in your query convert the datetime to date and filter the incoming values. Other wise use a range of datetime to determine the date you need > '2016-4-1 11:59:59' and < '2016-4-3 00:00:01'
Never underestimate the power of human stupidity RAH
-
If Oracle has a DATE type (no time component) then in your query convert the datetime to date and filter the incoming values. Other wise use a range of datetime to determine the date you need > '2016-4-1 11:59:59' and < '2016-4-3 00:00:01'
Never underestimate the power of human stupidity RAH
Now I Use T-SQL select Moto.ne_code, Moto.sta_datetime, convert(varchar(10),sta_datetime,111) as Sdate, convert(time,sta_datetime) as S_Time, Moto.province_id, Moto.ne_id, bss_cssr cssrv1, tchtc traffic, cssr cssrv2, hosr_v2 hosrv2, dropc dcr, dcr_v2 dcrv2, cavl calvol, hansr hosr, tchblor tchblr, sdcchbr sdcchblkr from OracleDBLink where convert(varchar(10),sta_datetime,111) = CONVERT(varchar(10), getdate()-1,111) and ((bss_cssr <=95) or (dropc>=3) or (hansr<=95)) and cavl>=50 it seem to be OK get the result as I Want. But I can Put it in the View. I Want to creat a job to do it everytime
-
If Oracle has a DATE type (no time component) then in your query convert the datetime to date and filter the incoming values. Other wise use a range of datetime to determine the date you need > '2016-4-1 11:59:59' and < '2016-4-3 00:00:01'
Never underestimate the power of human stupidity RAH
Oracle has two major temporal types, Date and Timestamp, where Timestamp has higher precision but less functionality. The easiest way to get a date without the time component is
Trunc(MyDate)Wrong is evil and must be defeated. - Jeff Ello