buzcut
asked on
converting a date that is in INT format in SQL to standard date format xx/xx/xxxx
I have a SQL server DB on the backend and a program that uses it written in Clarion 5 on the front end. Problem is Clarion is writing dates and times as date type INT (length 4) and I am now using MS Access 2003 for reports and some queries and I don't know where and how to convert the data. For example, the data is as follows: date format= 74300, and the time format= 3578973. The programmer I used suggested the following code: CAST(PMT_DATE - 36163 AS datetime). Problem is, I have no clue where to put it or how to execute the code. Any help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is your program in Clarion just writing to the DB or also reading data?
ASKER
the front end is written in Clarion and it reads and writes data to the SQL Server tables.
buzcut... have you tried the view method?
Jay
Jay
ASKER
Jay-
your instructions worked great! I can see the regular date now... but the other part of this question was how do I create a new table based on the new view? See, I want to have the table store the date as datetime and not as an INT so I can continue to work with the DB from Access, since I am replacing the Clarion front end. Thanks again!!!
Ed
your instructions worked great! I can see the regular date now... but the other part of this question was how do I create a new table based on the new view? See, I want to have the table store the date as datetime and not as an INT so I can continue to work with the DB from Access, since I am replacing the Clarion front end. Thanks again!!!
Ed
ASKER
Jay-
I just figured out how to create a table with the new definitions and converted data. Again, thank you for all of your help!!!
Ed
I just figured out how to create a table with the new definitions and converted data. Again, thank you for all of your help!!!
Ed
Cool
Enjoy :)
Enjoy :)