| Author |
Topic |
|
setshot50
Starting Member
5 Posts |
 Posted - 2005-06-17 : 10:01:30
|
| I am trying to run a query on a database that has a Date/Time entry represented by a 10 digit integer. Is there a SQL query that I can run the reformat this data into a readable format? Thanks, in advance. |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-17 : 10:04:19
|
| Handle presentation issues in the presentation layer (i.e. your application code) not at the database level.Just had this discussion...http://www.dbforums.com/t1165322.htmlSean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
 |
|
|
setshot50
Starting Member
5 Posts |
Posted - 2005-06-17 : 10:29:12
|
| Thanks for your reply. I'm relatively new to doing any type of advanced SQL and db manipulation so forgive me if these questions are relatively elementary. My situation is such that I have a date/time field giving me "1104478809" after importing the data into Excel, but I have no idea what this value means and need to run a query on the data for a given time span. Any help is greatly appreciated. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-17 : 10:46:13
|
| How did you import data to Excel?MadhivananFailing to plan is Planning to fail |
|
|
|
setshot50
Starting Member
5 Posts |
Posted - 2005-06-17 : 10:47:35
|
| Via a remote ODBC connection to SQL Server. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-17 : 10:59:02
|
| WellDid that table store the date in Datetime field?If so, there wont be any problem in that date field in ExcelMadhivananFailing to plan is Planning to fail |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-17 : 11:32:29
|
quote:
Originally posted by setshot50
Thanks for your reply. I'm relatively new to doing any type of advanced SQL and db manipulation so forgive me if these questions are relatively elementary. My situation is such that I have a date/time field giving me "1104478809" after importing the data into Excel, but I have no idea what this value means and need to run a query on the data for a given time span. Any help is greatly appreciated.
Wait -- are you in Excel or SQL Server? Did you import data from SQL into Excel, and now it doesn't look right? Or did you import the data FROM Excel TO SQL server, and it came in a numbers?If you are in Excel and things don't look right, then you simply format the cells as Dates and you are good to go.- Jeff |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-17 : 12:21:54
|
| He has bigger problems: "but I have no idea what this value means "How do even know it is a date and how do you expect to convert it if you can not decipher it.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
|
|
|
setshot50
Starting Member
5 Posts |
Posted - 2005-06-17 : 12:22:33
|
| Thanks Jeff. I imported the data FROM SQL Server TO Excel. Is it a mere action of placing a formula within Excel to convert the date/time pulled? Does "=B2/86400+DATE(1970,1,1)" sound right for the conversion? |
|
|
|
setshot50
Starting Member
5 Posts |
Posted - 2005-06-17 : 12:24:50
|
quote:
Originally posted by Thrasymachus
He has bigger problems: "but I have no idea what this value means "How do even know it is a date and how do you expect to convert it if you can not decipher it.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
I know it is a date because of the column header. I would assume there would be some type of standard protocol formula to convert the date/time data generated by SQL Server into a workable format. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-17 : 12:26:17
|
| Did you try formatting the cells as dates to see if that worked in Excel? Remember that internally, Excel stores all dates as numbers.- Jeff |
|
|
|
|
Converting Date/Time into readable format