SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 (date)Time help needed
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

roncdf
Starting Member

13 Posts

Posted - 11/21/2006 :  08:15:29  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  08:19:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/21/2006 08:20:30
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/21/2006 :  08:37:46  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  08:45:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  08:49:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 11/21/2006 :  08:53:00  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  08:55:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/21/2006 08:57:54
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/21/2006 :  09:02:17  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  09:04:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/21/2006 09:05:59
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/21/2006 :  09:08:41  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  09:10:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Show us what you got, then.
All queries and code to insert dates.
Also post some more sample data.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 11/21/2006 09:12:21
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/21/2006 :  09:26:46  Show Profile  Reply with Quote
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

Edited by - roncdf on 11/21/2006 09:27:38
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/21/2006 :  09:26:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  09:30:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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'


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 11/21/2006 :  09:48:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 11/21/2006 09:49:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/21/2006 :  09:51:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 11/21/2006 :  10:02:08  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2006 :  10:05:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/21/2006 10:09:16
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/21/2006 :  10:10:27  Show Profile  Reply with Quote
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..

Edited by - roncdf on 11/21/2006 10:11:40
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/21/2006 :  10:15:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/21/2006 :  10:22:23  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000