Problem with Date fields - 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

Problem with Date fields

Author

Topic

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-27 : 16:16:01

Hello,

I have this query:

USE M2MDATA01

SELECT SLCDPM.FCUSTNO as "Customer Number"
,SLCDPM.FCOMPANY as "Account Name:"
,SLSPNS.flastname as "Sales Rep"
,SLCDPM2.fcompany AS "Distributor"
,SLCDPM.fterr AS "Territory"
,SLCDPM.FUSERCODE "Market Channel"
--,SLCDPM.fcreated AS "Create Date"
,convert (varchar(10),SLCDPM.fcreated,110) AS "Create Date"
,convert(varchar(10),SLCDPM.fsince, 110) as "Last Modified"
FROM SLCDPM
LEFT JOIN SLCDPM AS SLCDPM2 ON SLCDPM.fdistno = SLCDPM2.fcustno
JOIN SLSPNS ON SLCDPM.fsalespn = SLSPNS.fsalespn
WHERE SLCDPM.ftype = 'C'
AND convert(varchar(10),SLCDPM.fsince, 110) BETWEEN '01-01-2014' and '01-11-2014'
ORDER BY 8

However; I am having issues with the date fields, my dates are incorrect. I tried converting the date field as DATE instead of varchar(10), fieldname, 110 and it tells me DATE is not a valid data type

Msg 243, Level 16, State 1, Line 3
Type date is not a defined system type.

Help? if I try converting the long date field to varchar(10) this is the output (which is not correct)

01-02-2008
01-03-2002
01-03-2002
01-03-2002
01-05-2004
01-06-2014
01-08-2009
01-09-2002
01-09-2002
01-10-2002
01-11-2008
01-11-2013

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 17:48:06

What version of SQL Server are you using? What is the compatibility level of this database? Date data type was added in SQL Server 2008 and does not exist in lower versions or in compatibility level 90 or lower.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 17:48:35

Are you just trying to strip the time?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-27 : 18:04:36

[code]AND SLCDPM.fsince>=convert(date,'01-01-2014',110)
AND SLCDPM.fsince<convert(date,'02-11-2014',110)[/code]

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-27 : 18:38:32

I'm using MS SQL server 2012...

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-27 : 18:40:09

Even if I try this:

select convert(date,getdate())

I get this error message:

Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 18:43:07

quote:
Originally posted by steve_joecool

I'm using MS SQL server 2012...

But what is the compatibility level of the database? I'll bet that it's 90, which doesn't have date date type.

Use this method to strip off time: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-28 : 12:23:28

Sorry Tara, I have tried viewing the compatibility level with this:

USE M2MDATA01 ;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'M2MDATA01';

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.databases'.

Is there a better way to check the compatibility level?

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-28 : 12:25:58

I found it!

USE M2MDATA01 ;
EXEC sp_helpdb

Compatibility level = 80

How do I change that?

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-28 : 12:39:40

Nevermind.....Google is my friend! :-) thanks for your help! that should take care of it.

steve_joecool
Starting Member

21 Posts

Posted - 2014-10-31 : 14:51:51

Tara,

I was unable to change the compatibility level, per the manufacturer of our system, this DB was setup to have a compatibility level of 80.

When I tried your method to strip the time off the date, still gives me 2014-10-31 00:00:00.000 as a result.

Any suggestions? my concern is that I need to create queries based on a time frame, and I don't know how to code it.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 14:55:31

Well it did strip off the time, meaning it zero'd it out. It's still a date/time column and thus you can't get rid of the time component. You'd have to convert to varchar with the appropriate style to remove the time portion.

But that is not needed just to create queries based on a timeframe. Just query like this:

WHERE Column10 >= DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) AND Column10 < DATEADD(dd, DATEDIFF(dd, 0, getdate()+1), 0)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources