Date/Time issue - 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

General SQL Server Forums

New to SQL Server Programming

Date/Time issue

Author

Topic

madd0g17
Starting Member

8 Posts

Posted - 2010-06-28 : 18:53:15

I am using the DotNetNuke report module that allows a sql statement to pull data from a SQL2005 server. Here's my issue, the dates for the specific database (a BridgeTrak80 database) is that the date/time is stored simply as YYYYMMDDHHMMSS instead of the standard SQL format for datetimes. Therefore, it will not let me do a "datediff()" with that value. Any ideas how I can possibly do something like:

where DATEDIFF(mm,IS_COMPLETED_DATE,NOW())=0

Any and all help is greatly appreciated!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-28 : 19:11:13

Your date/time is stores as a varchar column? There are a several ways to convert it to a DATETIME. Here is one:

WHERE
	DATEDIFF(MINUTE, CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(STUFF(IS_COMPLETED_DATE, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0 , ':'), 120), CURRENT_TIMESTAMP)

madd0g17
Starting Member

8 Posts

Posted - 2010-06-28 : 20:10:53

what do I replace "STUFF" with?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-29 : 02:40:40

quote:
Originally posted by Lamprey

Your date/time is stores as a varchar column? There are a several ways to convert it to a DATETIME. Here is one:
WHERE
DATEDIFF(MINUTE, CONVERT(DATETIME, STUFF(STUFF(STUFF(STUFF(STUFF(IS_COMPLETED_DATE, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0 , ':'), 120), CURRENT_TIMESTAMP)

YYYYMMDD HH:MM:SS is the unambiguous format. If you want to use hyphen use Time seperator too. ie YYYY-MM-DDTHH:MM:SS
Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources