convert time to 00:00:00 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert time to 00:00:00

Author

Topic

maarten.del
Starting Member

17 Posts

Posted - 2010-05-18 : 09:21:16

With help from this forum i created this query:

----------------------------------------------------------
INSERT INTO [mona].[dbo].[TB_CR_Tellerstanden_LOT_2]
([RtuTimeStamp]
,[Tag]
,[Value])

select ServerTimeStamp,
TagName,
MeasValue
from (select ServerTimeStamp,
TagName,
MeasValue,
ROW_NUMBER() OVER (PARTITION BY TagName, DATEDIFF(Day, 0, ServerTimeStamp) ORDER BY ServerTimeStamp DESC) AS recID2
from RawLog
where ServerTimestamp >= DATEADD(Day, datediff(day, 9, getdate()), 0) and ServerTimestamp < Dateadd(day, datediff(day, 0, Getdate()),1) and TagName like '%ATE%'
) AS e
where recID2 = 1 order by Tagname
----------------------------------------------------------

This query gives me for each Tagname, for each day, the last value for this day.
(I know, its not easy)
Result:

2010-05-18 15:00:02.010 ST102ATE01 4724905
2010-05-18 15:00:02.020 ST102ATE02 4749045
2010-05-18 15:00:01.990 ST103ATE01 1939780
2010-05-18 15:00:01.990 ST103ATE02 651866
2010-05-18 15:00:01.990 ST103ATE03 793380
2010-05-18 15:00:01.990 ST103ATE04 834313
2010-05-18 15:00:01.990 ST103ATE05 724517
2010-05-18 15:00:09.040 ST103ATE06 779844
2010-05-18 15:00:09.150 ST103ATE07 10049
2010-05-18 15:00:02.010 ST103ATE08 1437222
2010-05-18 01:00:00.000 ST203ATE01F 217137
2010-05-17 01:00:00.000 ST203ATE01F 216072
2010-05-16 01:00:00.000 ST203ATE01F 215216
2010-05-15 01:00:00.000 ST203ATE01F 214272
2010-05-14 01:00:00.000 ST203ATE01F 213215
2010-05-13 01:00:00.000 ST203ATE01F 212376
2010-05-12 01:00:00.000 ST203ATE01F 211422
2010-05-11 01:00:00.000 ST203ATE01F 210368
2010-05-10 01:00:00.000 ST203ATE01F 209320
2010-05-09 01:00:00.000 ST203ATE01F 208488

I have times like this:
15:00:02.010 and 01:00:00.000
I don't want that.
I want that all the times are like this 00:00:00.000

Can someone help me please?

Regards

Maarten

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-18 : 09:26:14

dateadd(day, datediff(day, 0, ServerTimeStamp), 0)

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-18 : 09:27:06

use dateadd(d, datediff(d, 0, ServerTimeStamp), 0)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-18 : 09:27:25

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-18 : 09:30:24

Re-write like this....

INSERT INTO [mona].[dbo].[TB_CR_Tellerstanden_LOT_2]
([RtuTimeStamp]
,[Tag]
,[Value])

select convert(varchar(10),ServerTimeStamp,101),
TagName,
MeasValue
from (select ServerTimeStamp,
TagName,
MeasValue,
ROW_NUMBER() OVER (PARTITION BY TagName, DATEDIFF(Day, 0, ServerTimeStamp) ORDER BY ServerTimeStamp DESC) AS recID2
from RawLog
where ServerTimestamp >= DATEADD(Day, datediff(day, 9, getdate()), 0) and ServerTimestamp < Dateadd(day, datediff(day, 0, Getdate()),1) and TagName like '%ATE%'
) AS e
where recID2 = 1 order by Tagname

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-18 : 09:41:15

quote:
Originally posted by senthil_nagore

Re-write like this....

INSERT INTO [mona].[dbo].[TB_CR_Tellerstanden_LOT_2]
([RtuTimeStamp]
,[Tag]
,[Value])

select convert(varchar(10),ServerTimeStamp,101),
TagName,
MeasValue
from (select ServerTimeStamp,
TagName,
MeasValue,
ROW_NUMBER() OVER (PARTITION BY TagName, DATEDIFF(Day, 0, ServerTimeStamp) ORDER BY ServerTimeStamp DESC) AS recID2
from RawLog
where ServerTimestamp >= DATEADD(Day, datediff(day, 9, getdate()), 0) and ServerTimestamp < Dateadd(day, datediff(day, 0, Getdate()),1) and TagName like '%ATE%'
) AS e
where recID2 = 1 order by Tagname

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

That will convert the datetime to string and in format MM/DD/YYYY. If the date format is not MDY, it will result in error in converting string to datetime. If you are going to convert to string, you should it convert to ISO format YYYYMMDD

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-18 : 09:46:44

ya correct,it should be

convert(varchar(10),ServerTimeStamp,112)

Thanks to point it out.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

maarten.del
Starting Member

17 Posts

Posted - 2010-05-18 : 09:56:32

What the fuck, sooooooo great...

It works...

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources