issue with date using convert function

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

General SQL Server Forums

New to SQL Server Programming

issue with date using convert function

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-21 : 07:23:43

Hi

when I insert data into datetime field it is giving me like this '2003-02-25 00:00:00.000' but actually it is 'Feb 25 2003 12:00:00:000AM'

the time factor is getting rounded.. any help in this regard

Regards,
aak

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 07:35:54

Hi,

It is not getting rounded. SQL saves the 12 AM time in that format.

Try to select this,

SELECT convert(varchar,convert(datetime,'2003-02-25 00:00:00.000'),100)

This give you,

Feb 25 2003 12:00AM

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-21 : 07:37:42

now when I teted it, and found that it is actually taking correctly only..

create table a
(a datetime)
insert into a
values (convert(datetime,convert(varchar(99),'Feb 25 2003 12:15:30:444PM',999),999))

insert into a
values (convert(datetime,convert(varchar(99),'Feb 25 2003 12:00:00:444AM',999),999))

select * from a

2003-02-25 12:15:30.443
2003-02-25 00:00:00.443

hmm I think it is because of AM PM if it is AM it considering as 00 for 12. correct me If I am wrong

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-21 : 07:49:21

Yes if 12 AM it saves like this

2003-02-25 00:00:00.000

and if 12 PM then it is

2003-02-25 12:00:00.000

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 07:57:22

midnight is AM (hour=00), noon is PM (hour=12)

Is that the issue?

note that milliseconds cannot be held accurately in DATETIME and may therefore be rounded - so your 444ms has displayed as 443ms

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-01-21 : 15:24:14

Thanks Kristen, ya I was bit confuse initally now I am fine with that

create table a (a datetime)
insert into a values (convert(varchar(99),'Feb 25 2003 12:00:00:444PM',999))
insert into a values (convert(datetime,'Feb 25 2003 12:00:00:444PM',999))

select * from a
2003-02-25 12:00:00.443
2003-02-25 12:00:00.443

both the insert gived me same result. however I used (convert(varchar(99),'Feb 25 2003 12:00:00:444PM',999)) to insert into datetime col hope it is not a problem ...correct me.
Regards,
aak

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 01:06:56

Please re-read my post here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138655#541151

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 01:50:52