SQL DateTime - 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

Development Tools

Other Development Tools

SQL DateTime

Author

Topic

sarikavbhosale
Starting Member

24 Posts

Posted - 2005-12-07 : 04:02:31

Hi thank you all of u....
I am facing the problem with fetching the record with VDateTime field in SQL server with type DateTime

I insert the record in the field by now function in vb .
which is stored in database as MM/dd/yyyy hh:mm:ss AMPM

But when i am fetching the record with format(now,"MM/dd/yyyy") the result not showing any record.
i.e.
temp=format(now,"dd/MM/yyyy")
i used the SQL query like this,

select VDateTime from VoiceRecorder where VDatetime like '%" & temp & " %'

Please give me the proper way to fire the query....
Will the sql server's datetime format affect's here?
plz it's very argent....

Thank u...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 04:20:46

Read this
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-07 : 04:25:22

first of all, lets make things clear.

In SQL Server, date and time is stored in datetime data type in it's own internal format not MM/dd/yyyy hh:mm:ss or dd/mm/yyyy hh:mm:ss

from SQL Server Books OnLine :

quote:
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So if you store both date and time component into your column, when retrieving from your table, either you specify exact date & time or use convert function. See below codes and it should give u a better idea.

create table #VoiceRecorder
(
	VData		integer identity(1,1),
	VDateTime	datetime
)

insert into #VoiceRecorder
select	'2005-12-01'		union all
select  '2005-12-02 13:34'	union all
select  '2005-12-02 04:15:01'   union all
select  '2005-12-02 08:38:34'   union all
select  '2005-12-02 23:59:00'  

select * from #VoiceRecorder

select VData, VDateTime from #VoiceRecorder where VDateTime = '2005-12-02 04:15:01'

select VData, VDateTime from #VoiceRecorder where convert(char(8), VDateTime, 112) = '20051202'

-----------------
[KH]

sarikavbhosale
Starting Member

24 Posts

Posted - 2005-12-07 : 04:40:51

Yes,SQL server stores the datetime in it's internal format....
Now i tried using DatePart function in sql ....
So it sepearte the year,month & date ..& now i am able to fetch the records...

thank u all of u....all my best wishes r always with u....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 04:51:30

>>Now i tried using DatePart function in sql ....

What is the need of using DatePart function?
Did you fully read the article I specified?

Madhivanan

Failing to plan is Planning to fail

sarikavbhosale
Starting Member

24 Posts

Posted - 2005-12-07 : 06:03:48

Yes i read ur article from thr only i use the DatePart function....they have specfied many of them.

Thank U.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources