Problem with Datetime format

Question

Post reply

Problem with Datetime format

Senthil T

Hall of Fame

Points: 3367
More actions
October 10, 2005 at 11:41 am

#87973

Hi,
I have a datetime value that I read from a database table. I want to pass this value back to a sproc to use it in the where clause, something like this, SELECT * FROM mytable WHERE mydate = @mydate
This statement did not return any rows. It did not make sense because I do get the @mydate value from the database and I am not changing the value anywhere in my code. The problem is, the datetime vlaue that I retrieve from the table is in this format '12/6/2004 11:07:58 PM'. But in the query analyzer, it shows up in this format '2004-12-06 23:07:58.323'. Can any one tell me how to fix this problem? How to convert between these two formats?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

surya-236315 SSC Eights! Points: 850 More actions · Answer 1

Senthil! try this.

SELECT * FROM mytable WHERE convert(varchar(25),mydate,13) = convert(varchar(25),@mydate,13)

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 2

What is the data-type for the variable to which you assign it after reading from the database ? Is it a string ? If so, then it will read the value in the default format (by default) : mon dd yyyy hh:miAM (or PM).

You can either use the datetime data-type instead or use the convert function (look up BOL for that) - however, since you are using the equality operator, please remember that the date has to be an exact match (including the milli-seconds).

create table t07 (col1 datetime)

insert into t07 values ('2004-12-06 23:07:58.323')

declare @col1 varchar(30)

select @col1 = col1 from t07

print @col1

select * from t07 where col1 = @col1

--no records

--convert it before reading:

select @col1 = convert(varchar(30), col1, 121) from t07

print @col1

select * from t07 where col1 = @col1

--you will get the record

--Use the right data-type

declare @col1 datetime

select @col1 = col1 from t07

select * from t07 where col1 = @col1

--you will get your record

Hth

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

I don't think the format is an issue UNLESS the data in your table is a VARCHAR, SQL should not care if you pass WHERE date = '2005-10-10 8:30AM' vs '10/10/05 8:30AM'

Have you validated by yourself that there is data for the hh/mm/ss/nnn you are looking for? It could be that there is simply no data that meets your criteria....

Good Hunting!

AJ Ahrens

webmaster@kritter.net