date conversion problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

date conversion problem

Author

Topic

yolip
Starting Member

4 Posts

Posted - 2006-05-11 : 20:28:39

Dear all,
I have a query which select data from a character field, it needs to be converted to date before doing the comparison. but the problem is it only accept the date formatted in "mm/dd/yyyy", it causes error whenever the format in '30/10/2006'. Is there any way i can do the formatting before the conversion? please give help urgently.

Thanks

SELECT extra_field7
FROM details
WHERE
extra_field7 is not null
and
(DATEDIFF([day], CAST(CONVERT(datetime, extra_field7, 105) AS datetime), GETDATE()) < 1)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-11 : 21:19:56

Try Using

SET DATEFORMAT DMY

-- Check with
Print getdate()

and after ur operation again do
SET DATEFORMAT MDY (to have ur original setting)

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-12 : 02:20:07

Use universal format yyyymmdd. Use 112 in place of 105. Also hereafter use proper DateTime datatype to avoid any kind of problem with dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources