Parsing a timestamp - 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

Parsing a timestamp

Author

Topic

Grayco
Starting Member

22 Posts

Posted - 2010-04-04 : 15:47:28

Greetings,
I'm working with time stamps with the following format.

"2010-04-04 00:00:00". This is 2 second data (YYYY-MM-DD hh:mm:ss)

I'm using the following in the WHERE statement to attempt to get one reading at 08:00 each morning.

WHERE (left(right(table.timestamp,5),1)=8)AND
(left(right(table.timestamp,6),1)=0)AND
(left(right(table.timestamp,4),1)=0)AND
(left(right(table.timestamp,3),1)=0)

What I've been able to get is timestamps form 08:00 and 20:00.

There are some odd behaviors in this query. The seconds are not recognized and the seconds/minutes ":" is not recognized but my sample sql ignors these oddities.

How can I see what the actual timestamp looks like and how can I only get 8AM data?

Thanks
Glen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-04 : 16:54:01

What data type is the timestamp column?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Grayco
Starting Member

22 Posts

Posted - 2010-04-04 : 20:57:38

I am not certain what the data type is. The timestamp is created and stored through one of our PLCs.

The left/right function appears to work and I don't have to put quotes on the numbers in the WHERE statment so I think it is numeric.

Is the a date/time data type?

Also I was incorrect about the format. It is yyyy-mm-dd hh:mm:ss.xxx
It is the .xxx that is not seen by the left/right statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-04 : 21:02:30

you can use below to see what is the data type of the column

sp_help <table name>

KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-05 : 12:49:29

Thanks for your reply. I ran it and got the following information;

Owner is dbo the type is user table and the creation datetime is
2009-06-12 15:05:46.513

quote:
Originally posted by khtan

you can use below to see what is the data type of the column
sp_help <table name>

KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-05 : 12:55:31

I just ran this and got other results.

sp_help "timestamp"

The results were;

type_name = timestamp
storage_type = timestamp
length = 8
prec = 8

Grayco
Starting Member

22 Posts

Posted - 2010-04-05 : 13:36:47

After a little more digging, I have identified the data type as ODBC Date-time format literal_type = ts

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-05 : 15:38:24

Are you using Microsoft SQL Server or some other dbms?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-05 : 18:16:02

quote:
Originally posted by Grayco

I just ran this and got other results.

sp_help "timestamp"

The results were;

type_name = timestamp
storage_type = timestamp
length = 8
prec = 8

your table name is "timestamp" ?


sp_help your_table_name

replace the bold string with your actual table name and execute it. You should see on the 2nd result set the detail information about your columns. Check the "Type" of your column "timestamp"

KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-05 : 20:46:04

The table name is "Waste_Water" and timestamp is a column.
when I ran sp_help "waste_water" I got;

Owner = dbo
type = user table
creation datetime = 2009-06-12 15:05:46.513

Grayco
Starting Member

22 Posts

Posted - 2010-04-05 : 20:51:23

I'm not certain. Is there a way to find out?

quote:
Originally posted by tkizer

Are you using Microsoft SQL Server or some other dbms?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-05 : 22:27:35

Run SELECT @@VERSION.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-05 : 22:32:49

quote:
Originally posted by Grayco

The table name is "Waste_Water" and timestamp is a column.
when I ran sp_help "waste_water" I got;

Owner = dbo
type = user table
creation datetime = 2009-06-12 15:05:46.513

OK. try this and post the result


select	TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from	INFORMATION_SCHEMA.COLUMNS
where	TABLE_NAME	= 'Waste_Water'
and	COLUMN_NAME	= 'timestamp'

KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-06 : 14:19:39

Result was Microsoft SQL Server 7.00 7.00.842

quote:
Originally posted by tkizer

Run SELECT @@VERSION.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Grayco
Starting Member

22 Posts

Posted - 2010-04-06 : 14:44:50

The query gave these results;
Table_catalog = FMS2_data
Table_schema = dbo
Table_name = Waste_water
Column_name = timestamp
Data_type = datetime
Character_maximum_length = (no data returned)
I added Datetime_precision = 3

quote:
Originally posted by khtan

quote:
Originally posted by Grayco

The table name is "Waste_Water" and timestamp is a column.
when I ran sp_help "waste_water" I got;

Owner = dbo
type = user table
creation datetime = 2009-06-12 15:05:46.513

OK. try this and post the result

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Waste_Water'
and COLUMN_NAME = 'timestamp'

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 16:19:05

This should give you 8am: DATEADD(hh, 8, DATEADD(Day, DATEDIFF(Day, 0, [timestamp]), 0))

I don't have SQL Server in front of me to test, but I believe that's the formula.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 20:57:08

now that we know you are using SQL Server 7 and the data type for the timestamp column is datetime.

you can write the where part as

where timestamp - dateadd(day, datediff(day, 0, timestamp), 0) = '08:00'

where convert(varchar(5), timestamp, 108) = '08:00'

this will only give you all the 08:00 record.

Are you looking for a specific day 08:00 record or all the 08:00 record ? If you are looking for a specific day's record, the query should be re-written in different way for better use of indexes

KH
[spoiler]Time is always against us[/spoiler]

Grayco
Starting Member

22 Posts

Posted - 2010-04-07 : 18:08:29

Hello Tkizer and Khtan,

The "where timestamp - dateadd(day, datediff(day, 0, timestamp), 0) = '08:00'"
Did not find any data. I think the seconds in the timestamp may have excluded all the records.

The "where convert(varchar(5), timestamp, 108) = '08:00'" worked perfectly.

Thank You both for your help and consideration.

Glen Ray

quote:
Originally posted by khtan

now that we know you are using SQL Server 7 and the data type for the timestamp column is datetime.

you can write the where part as

where timestamp - dateadd(day, datediff(day, 0, timestamp), 0) = '08:00'

or

where convert(varchar(5), timestamp, 108) = '08:00'

this will only give you all the 08:00 record.

Are you looking for a specific day 08:00 record or all the 08:00 record ? If you are looking for a specific day's record, the query should be re-written in different way for better use of indexes

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources