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 2000 Forums
 SQL Server Development (2000)
 (date)Time help needed

Author  Topic 

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 08:15:29
I have a datetime field in my MSSQL db. With the following format:
DD-MM-YYYY HH:MM:SS
Thats how i insert into my database using a C# program. Now if i then use a query to search results with that datetime it doestnt find anything. (I use conversion: TestingDate = CONVERT(datetime, '21-11-2006 13:21:29', 103)

When i get all data of the table using Enterprise Manager and i press enter in the datetime field (to probably insert it again), a query on the datetime suddenly works.
So does the Enterprise Manager does some kind of conversion of the data? And can i do does with the insert query? I have no clue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 08:19:50
Do not bother how the dates are presented!
The internal representation of a date is a decimal value, where the integer part is the number of days passed since January 1, 1900.
And the fraction is the number of 1/300's of a second passed since midnight.

If using varchar to store dates, always use ISO date format (120 or 121) to search and seek.

See this topic how to pass parameters to SP from desktop/web application.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 08:37:46
thanks for the reply.

But im using a large database from a company. The company probably has the datetime fields in the database set like my first post. If i input another format it automatically formats to that format. I just cant be able to seem to read it out correctly.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 08:45:45
Do not bother how the dates are presented!
SINCE THE FIELD/COLUMN IS OF TYPE DATETIME, SQL SERVER TAKES CARE OF THE STORAGE AUTOMATICALLY!

Even if you insert this value, 20061118 13:34:45, it would still be presented as, 18-11-2006 13:34:45...



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 08:49:07
Worry about the presentation about the dates in the front-end application! (Copyright 2006 Madhivanan)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 08:53:00
Yes ok, but that was not the question. Maybe i dont get it, but i know for sure there is a correct DateTime value in the database, it does not matter how its displayed.
So i have a correct value in the database, displaying in the format i want it to display. So why can't i just do a normal query on it and find the value? If i can input it, why can i not get it out?

So i dont understand where this ISO format comes in. You mean i have to parse the date to another format to get it out of the database?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 08:55:54
If the column data type is DATETIME/SMALLDATETIME, SQL Server will always assure the date is valid.
If the column data type is VARCHAR, use

SELECT * FROM YourTable
WHERE ISDATE(YourColumn) = 0

to get all invalid dates from the table.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 09:02:17
The field is DateTime.

See my first question for the answer i want to know. I will eleborate again.

In my application i add a CORRECT datetime value INTO the database. When i do a search on for example the tableNr it finds the CORRECT ROW with the datetime. When i do a search on the datetime, it DOES NOT find the correct ROW. Only if i manually INSERT the row again using the manager it DOES work.

I hope i explained it good enough like that. As i said i have no clue whats going on. It seems to me you cant insert an incorrect date + time into the field.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:04:16
IT WILL FIND THE CORRECT ROW IF YOU USE ISO DATEFORMAT!

SELECT * FROM TableNr WHERE DateTimeColumn = '20061121 13:21:29'

Try that!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 09:08:41
I tried different formats already, as all others that one does not work also
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:10:14
Show us what you got, then.
All queries and code to insert dates.
Also post some more sample data.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 09:26:46
Table:
[TestNr] [numeric](10, 0) IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[MethodID] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[SampleNr] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[TestingDate] [datetime] NOT NULL ,
[HistoryNr] [numeric](18, 0) NULL ,
[Tms] [timestamp] NOT NULL ,
[Status] [char] (10) COLLATE Latin1_General_CI_AS NULL

I insert into the table using a Stored Procedure, its automatically generated and also encrypted so i cant give it atm.
Then i just do a query like above to try and get it out.

Im letting the server administrator look it at, so i doubt you can help me any further. Anyway thx for your time.
I had hoped there was an easy answer, but thats probably not the case
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:26:58
Read this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74738 so we don't have to go through that evolving and escalating discussion again.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:30:58
[code]declare @Table table (
[TestNr] [numeric](10, 0) IDENTITY (1, 1),
[MethodID] [varchar] (20) COLLATE Latin1_General_CI_AS,
[SampleNr] [varchar] (20) COLLATE Latin1_General_CI_AS,
[TestingDate] [datetime],
[HistoryNr] [numeric](18, 0),
[Tms] [timestamp],
[Status] [char] (10) COLLATE Latin1_General_CI_AS
)

insert @table (testingdate) values('20061121 12:15:45')
insert @table (testingdate) values('10/12/2006 11:33:20')
insert @table (testingdate) values('12/10/2006 22:07')

select * from @table

select * from @table
where testingdate = '20061210 22:07'[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-21 : 09:48:51
probably your time portion is preventing you from getting correct results.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 09:51:58
Don't spoil the fun!
Obviously he must have told us from the start if the solution is that easy, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 10:02:08
spirit1 has given me a 10 times better answer then you did without waisting an hour of your and my time. I probably dont share your sense of humor :)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 10:05:52
I asked you a long time ago (almost an hour) to post the code where you do the search, but you didn't bother
I am sure we would have found the answer there. And I hopes this lesson have given you some insights in DATETIME datatype.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 2006-11-21 : 10:10:27
SELECT * FROM TEST WHERE TestingDate = CONVERT(datetime, '21-11-2006 13:21:29', 103)
OR WHERE TestingDate = '21-11-2006 13:21:29'
OR WHERE TestingDate = '20061121 13:21:29'
etc ( i tryed alot of them)

The code just generates this SQL..
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 10:15:23
You don't need to convert the date if testingdate is datetime or smalldatetime already.
The code above is "generated" you wrote. Where is it generated?

If you want all records for a specific date, use

select * from test
where dateadd(day, datediff(day, 0, testingdate), 0) = '20061121 00:00:00'

or

select * from test
where testingdate >= '20061121 00:00:00' and testingdate < '20061122 00:00:00'

You still haven't explained the purpose of the SELECT query you use.
Which records are you filtering? With what?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 10:22:23
As Peter already explained, internally a datetime is stored as a decimal and converted to a date and a time when you query. The time includes milliseconds so your query is probably returning nothing because you are asking for rows that match a specific time and you are not specifying milliseconds. So you need to compare what you do know, like this (if you really want to find a row based on a time down to the second).

SELECT * FROM TEST WHERE CONVERT(varchar(19), TestingDate, 120) = '2006-11-21 13:21:29'
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -