Problem with Timestamps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Timestamps

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-18 : 06:53:46

Hello,

I was trying to create a graph in excel, pasting a column with timestamps from my sql database into it. Unfortunaltely, Excel modifies some of the timestamps and the other ones don't, so that my data format is not unique and I can not use it for my graph.

This is how my timestamps look like:
2005-11-22 09:02:31.000

Is it possible to write a query, that gives back only the hours and minutes of the timestamp, like 09:02 using the example above.

Tnx and regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 07:33:00

you've format cells property in excel to do this. just select the column in excel with dates and use format cell properties and you've lots of formats for date available under custom formats.

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-18 : 07:40:36

already did this, 10% of the cells are still in the wrong format..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 07:43:53

quote:
Originally posted by rocco2008

already did this, 10% of the cells are still in the wrong format..

why? does your source date format also vary? then try using convert. use
CONVERT(varchar(5),datecol,108)

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-20 : 06:12:32

Hi, yes the source is all the same. I used the convert function, but I get the error #NAME.

Lets say this is cell A1, should work like this right:

=CONVERT(varchar(5),A1,108)

many tnx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:19:56

quote:
Originally posted by rocco2008

Hi, yes the source is all the same. I used the convert function, but I get the error #NAME.

Lets say this is cell A1, should work like this right:

=CONVERT(varchar(5),A1,108)

many tnx

nope. you need to do this convertion in sql table bring you bring data to excel.

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 12:30:41

I don't have writing rights on the server.. any other idea?

Also, the source data in SQL is unique! As I was trying to copy and paste the same data at my workplace, everything was correctly shown!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-21 : 12:44:42

Add a column in Excel, format the new column for date time.
Copy the data from the original colun and past special values only into the new column.
Delete the original column

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 12:50:16

It still won't work..

Like I said the only time it worked was on a workstation at my workplace. But on any other computer I tried it failed!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-21 : 12:52:09

Can you post some of the dates that are not converting?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 13:07:48

sure, the following dates are continuous and copied directly out of the sql table. the first two get converted correctly, the last two won't. they won't even convert if I try to format the cells again after I already pasted them.

2007-10-08 08:50:19.380
2007-10-08 08:50:19.970
2007-10-08 08:50:24.050
2007-10-08 08:50:27.020

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-21 : 13:15:08

Then I would look at the Excel on the machine you are having trouble with.
It works fine here.

Copy the values you pasted from this website and paste them into Excel.
If it still does not work you definately have an Excel issue.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 13:26:11

I wanna cry! it won't even work if I copy and paste it from the website.. i have constant access to 3 machines and it is not working on any one of them :(

what could be the issue??

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-21 : 13:32:16

Excel... Check the service packs and the OS date formatting.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 13:55:47

Everything is up to date...

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-21 : 13:58:03

Try Open Office :)
The problem (if the paste from this website to a new XLS doc didn't work) is an Excel issue

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 17:34:48

alright, I figured the problem: my excel can't handle timestamps, which have a zero as the first positions for the 1/1000 seconds, eg

2005-11-22 09:02:31.050

is it possible to write a query, which is just returning the hours and minutes of each timestamp in the column?? (eg 09:02 from above example)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 17:44:25

Custom format your cell: YYYY-mm-dd h:mm:ss.000

or add " ' " to display it as a string:

'2005-11-22 09:02:31.050

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-21 : 18:04:45

I was hoping for a sql query, which will just return hh:mm! this would make a lot of things much easier for me, because that's the actual information I need and most importantaly excel just won't know what to do with the cell if the 1/1000 sec starts with a 0. since I have several thousand timestamps, I can't just change them manually..

eg:
2005-11-22 09:02:31.050, won't be recognized by excel, it will just print it as it is as a string and no matter how I format it, it won't change to be a string..

BUT:
2005-11-22 09:02:31.150, will be recognized and let me changes the format without a problem.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 18:18:04

select
right('00'+convert(varchar(2),datepart(hh,convert(datetime,'2005-11-22 09:02:31.150'))),2)
+ ':' +
right('00'+convert(varchar(2),datepart(minute,convert(datetime,'2005-11-22 09:02:31.150'))),2)
gives you: '09:02'

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 18:22:15

I don't know bro, Excel works fine for me on your timestamp formats when I use custom format.
Plus, you will run into the same problem again on another computer with different region format.

For inline Conversion:

=TEXT(A1,"yyyy-mm-dd hh:mm:ss.000")

Assume A1 is your timestamp column

Here is how you do it with custom format...

Click on the column header to High light the entire column
Then right click:

Format Cell -> Catagory: Custom (all the way down...)
Under "Type:" Enter (the right side panel)
yyyy-mm-dd hh:mm:ss.000

click OK

Of course if you just want HH:MM just type in hh:mm in the custom format.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources