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:SSThats 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=70783Peter LarssonHelsingborg, Sweden |
|
|
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. |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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? |
|
|
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, useSELECT * FROM YourTable WHERE ISDATE(YourColumn) = 0to get all invalid dates from the table.Peter LarssonHelsingborg, Sweden |
|
|
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. |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 NULLI 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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 @tableselect * from @tablewhere testingdate = '20061210 22:07'[/code]Peter LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 :) |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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.. |
|
|
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, useselect * from testwhere dateadd(day, datediff(day, 0, testingdate), 0) = '20061121 00:00:00'orselect * from testwhere 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 LarssonHelsingborg, Sweden |
|
|
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' |
|
|
Previous Page&nsp;
Next Page
|