Is it possible to convert an integer field in SQL to a time field in a view
I have a SQL 2005 system that we can't change the structure of format for... but have another system that needs to read the data.
We can create SQL views in the first system
The problem is that the first system has a Date field, but no time field ... so we have an issue bringing in multiple records from the same day.
The first system does have an integer field that increments, but the 2nd system can't have fields added.... but, if we could see this as a time, would could use it for duplicate checking.
I'm not a SQL expert, but I'd like to know if there's a way we can have the integer field in the SQL table show as a time field in a custom view.
Any chance?
We can create SQL views in the first system
The problem is that the first system has a Date field, but no time field ... so we have an issue bringing in multiple records from the same day.
The first system does have an integer field that increments, but the 2nd system can't have fields added.... but, if we could see this as a time, would could use it for duplicate checking.
I'm not a SQL expert, but I'd like to know if there's a way we can have the integer field in the SQL table show as a time field in a custom view.
Any chance?
Anuj
Can you post provide sample data?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
can you please show some concrete data about what you have, and what you want to be returned from that data?
Select Cast(0.1 as Datetime)
Select Cast(1 as Datetime)
From what I can gather from your question this should work.
Select Cast(1 as Datetime)
From what I can gather from your question this should work.
ASKER
There is a date field - actually a datetime field with all the times as 00:00:00.000
We are using that. So need the integer field to so as xx:xx:xx.xxx
Doesn't matter if how the times are interpreted... we just really want to use it as a unique id for dupe checking
I'll try to get some sample data tomorrow if that doesn't make sense
We are using that. So need the integer field to so as xx:xx:xx.xxx
Doesn't matter if how the times are interpreted... we just really want to use it as a unique id for dupe checking
I'll try to get some sample data tomorrow if that doesn't make sense
still not clear ... either the data type int vs date/time is irrelevant, or your explanation is simply misleading for me.
please post sample data-
please post sample data-
If you first divide the integer by, say, 1000, it should give you what you need.
Select cast(IntegerField/1000 as datetime)
This will give you a zero date (1900-01-01) with an associated time.
Select cast(IntegerField/1000 as datetime)
This will give you a zero date (1900-01-01) with an associated time.
I'm not sure I know exactly what you need, but here's my best guess.
--existing CREATE VIEW
CREATE VIEW ...
AS
SELECT
...other_columns...,
DATEADD(MILLISECOND,
CAST(dateOnlyColumn AS datetime), --date-only column in first system
int_Increment --int column that increments
), --create new datetime columns for second system, where time portion is just a reflection of the increment value, not a "real" time
...,other_columns...
FROM ...
...
--existing CREATE VIEW
CREATE VIEW ...
AS
SELECT
...other_columns...,
DATEADD(MILLISECOND,
CAST(dateOnlyColumn AS datetime), --date-only column in first system
int_Increment --int column that increments
), --create new datetime columns for second system, where time portion is just a reflection of the increment value, not a "real" time
...,other_columns...
FROM ...
...
CORRECTION:
Obviously put the DATEADD arguments in the wrong order (was too busy explaining it :-) ):
DATEADD(MILLISECOND,
int_Increment, --int column that increments
CAST(dateOnlyColumn AS datetime) --date-only column in first system
),
Obviously put the DATEADD arguments in the wrong order (was too busy explaining it :-) ):
DATEADD(MILLISECOND,
int_Increment, --int column that increments
CAST(dateOnlyColumn AS datetime) --date-only column in first system
),
ASKER
Just to confirm ... this can be done in the View without modifying the SQL tables?
I've only used views to display the data from the tables in an easier to use form ... ie combining data from multiple tables into a single one.
I've only used views to display the data from the tables in an easier to use form ... ie combining data from multiple tables into a single one.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Scott, you're creating a new incremental data in the milisecond field, right?
That's not what we need to do ... there is an integrar field in the SQL that I want to bring into the minute and hour parts of a time field - ie to use data already there
That's not what we need to do ... there is an integrar field in the SQL that I want to bring into the minute and hour parts of a time field - ie to use data already there
No, I'm adding whatever your integer value was as milliseconds to SQL.
Naturally if you prefer you could add it as seconds, or minutes, instead.
I used milliseconds to cover larger values, and because you showed the format as "00:00:00.000".
Naturally if you prefer you could add it as seconds, or minutes, instead.
I used milliseconds to cover larger values, and because you showed the format as "00:00:00.000".