Nvarchar to date?? - 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 2005 Forums

Transact-SQL (2005)

Nvarchar to date??

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-11-20 : 07:41:25

I am trying to do a search on a Nvarchar field that has dates like 01-16-12

Here is what I am trying, with no luck
SELECT dbo.tbl_Name.TermExpires
FROM dbo.tbl_Name
WHERE (CONVERT(varchar(10), dbo.tbl_Name.TermExpires, 101) > '12/15/07')

ORDER BY dbo.tbl_Name.LastName

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-20 : 07:44:20

try converting it to a datetime instead of a varchar....

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-20 : 07:48:28

1 Always use proper DATETIME datatype to store dates
2 www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-11-20 : 07:50:27

Not quite sure how to do that

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-20 : 07:54:08

SELECT dbo.tbl_Name.TermExpires
FROM dbo.tbl_Name
WHERE CONVERT(datetime, dbo.tbl_Name.TermExpires) > '2007-12-15'
and ISDATE(dbo.tbl_Name.TermExpires)=1

Madhivanan

Failing to plan is Planning to fail

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-11-20 : 08:03:27

I am getting

Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-11-20 : 08:24:33

WHERE (ISDATE(dbo.tbl_Name.TermExpires)=1) and ((CONVERT(DATETIME, dbo.tbl_Name.TermExpires) > '2007-12-15') AND (CONVERT(DATETIME, dbo.tbl_Name.TermExpires) < '2008-01-31'))

Now is giving me

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-11-20 : 09:54:38

It was bad data There was a date with 01-01-106

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-20 : 12:42:07

quote:
Originally posted by helixpoint

It was bad data There was a date with 01-01-106

Which is why, as Madhi said, you should always use the proper datetime datatype to store dates.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 13:00:06

This article by Jeff Smith is certainly worth a read (and hey, it'll only take you 2 minutes and be invaluable to you!)

http://weblogs.sqlteam.com/jeffs/archive/2007/07/03/60248.aspx

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources