Conversion failed when converting date and/or time

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

Conversion failed when converting date and/or time

Author

Topic

friend
Starting Member

6 Posts

Posted - 2012-01-13 : 02:12:45

Hi all,

my date format is dd/mm/yyyy.

i want to compare database table field with today's date
,but i am getting error in the below mentioned line
declare @today date; //date format is dd/MM/yyyy
EffectiveDate is also datatype date in tblemp table;

set @today = CONVERT(VARCHAR(10),GETDATE(), 103);
select * from tblemp where isnull(CONVERT(VARCHAR(10), cast(EffectiveDate as datetime), 101), @today) <= @today
Regards
Rama

Friend

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-13 : 02:21:53

quote:
EffectiveDate is also datatype date in tblemp table;

Since EffectiveDate is date data type, you don't have to do any conversion at all


SELECT *
FROM   tblemp
WHERE  EffectiveDate <= convert(date, getdate())

KH
[spoiler]Time is always against us[/spoiler]

friend
Starting Member

6 Posts

Posted - 2012-01-13 : 02:26:27

in database table field EffectiveDate='2012-10-01'(yyyy-dd-mm)

select convert(date, getdate())=2012-01-13(yyyy-mm-dd)

Can you please guide me

Friend

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-13 : 02:30:29

quote:
Originally posted by friend

in database table field EffectiveDate='2012-10-01'(yyyy-dd-mm)

select convert(date, getdate())=2012-01-13(yyyy-mm-dd)

Can you please guide me

Friend

What is the question ?

KH
[spoiler]Time is always against us[/spoiler]

friend
Starting Member

6 Posts

Posted - 2012-01-13 : 02:43:54

in database table field EffectiveDate='2012-10-01'(yyyy-dd-mm)

select convert(date, getdate())=2012-01-13(yyyy-mm-dd)

both dateformats are different so how can i compare:

select * from tblemp where isnull(CONVERT(VARCHAR(10), cast(EffectiveDate as datetime), 101), @today) <= @today
can you please let me know.

Friend

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-13 : 03:06:35

Date is not stored in database in any particular format. It is in-fact in binary format.

When comparing date, you don't need to convert it to string to compare which you did in your query.

the following sample query will works just fine.


SELECT *
FROM   tblemp
WHERE  EffectiveDate <= convert(date, getdate())

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-13 : 03:09:29

Also when you run the query


select convert(date, getdate())

The date result you see in YYYY-MM-DD is how the Query Window format the date and present to you. It does not represent how the date is stored in the database.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources