Date and time -- sql server new users

sql server new users : Date and time

Jens
9/27/2006 7:02:17 AM

Hi,

simply use the datediff funtion for that:
DATEDIFF(dd,GETDATE(),'2006-02-20')

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Jens
9/27/2006 8:46:32 AM

That is because either one string can not be converted (Use the ISDATE)
for that, or the string is is a non convertible format (because its
char(1000) instead of a varchar.

HTH, jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Arnie Rowland
9/27/2006 8:50:17 AM

In the future, please don't use reserved words for table and column names.
[Date] is a reserved word. If you 'inherit' situations where reserved words
have been used and you are stuck with them, you MUST remember to ALWAYS use
square brackets [] on each side of the reserved word.

Also, I noticed that in your WHERE clause below, that you have [Date0] as
the column name? From your earlier message, it appeared as [Date].

Is it [Date] or [Date0]?

(Using [Date0] will cause the error message that you are receiving.)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

[quoted text, click to view]

Garth
9/27/2006 9:48:32 AM

I am having an issue with a query that is trying to compare a string date to
today's date

I have a table like this

PC Name
Date (year-month-day) (Varchar)

PC1
2006-02-20

PC2
2008-11-11

PC3
2007-08-29

Etc.
Etc.

So far if I try to convert the varchar to datatime I get an error "Syntax
error converting datetime from character string." when it is within the
where clause. It works find when it is within the Select section of the
query

How can I compare today's date against the table to find all PCs with a date
older than today?

Garth
9/27/2006 10:29:19 AM

Un-fortunately that is not working, when I use "and DATEDIFF(dd, getdate(),
tablename.Date0) < 0" I still get

Server: Msg 241, Level 16, State 1, Line 6

Syntax error converting datetime from character string.

When it is converting from a string how does it know that the date is
formatted as yyyy-mm-dd or how can I force it to know that the string date
are yyyy-mm-dd?

[quoted text, click to view]

Arnie Rowland
9/27/2006 11:31:56 AM

I have to agree with Jens (above). Verify that all rows have valid datetime
values in the v_GS_Warranty_Summary0.HWEndDate0 field.

Looking just three lines above the 'offending' line, I notice that you are
testing for a value of 'n/a'. That cannot evaluate to a valid datetime
value, and the datediff function will fail. So if you do indeed have the
value 'n/a' stored in the column, you will have to eliminate those
non-conforming columns first.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

[quoted text, click to view]

Garth
9/27/2006 2:19:30 PM

The real column name is HWEndDate0

declare @now as datetime
set @now = convert(smalldatetime,Getdate(),120)

select
v_GS_COMPUTER_SYSTEM.name0 as 'PC Name',
v_GS_COMPUTER_SYSTEM.username0 as 'User id',
v_GS_Warranty_Summary0.manufacture0 as 'Manufacturer',
v_GS_Warranty_Summary0.Model0 as 'Model',
convert(smalldatetime,v_GS_Warranty_Summary0.HWEndDate0,120) as date,
@now as 'now'
from
v_GS_Warranty_Summary0 v_GS_Warranty_Summary0,
v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM
where
v_GS_Warranty_Summary0.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
and v_GS_Warranty_Summary0.ResourceID not in(select
v_GS_Warranty_Summary0.ResourceID from v_GS_Warranty_Summary0 where
v_GS_Warranty_Summary0.HWEndDate0 in ('n/a','Not Applicable'))
and v_GS_Warranty_Summary0.HWEndDate0 is not NULL
and isdate(v_GS_Warranty_Summary0.HWEndDate0) = 1
and DATEDIFF(dd, getdate(),v_GS_Warranty_Summary0.HWEndDate0) < 0

Here is the full blown query.

If I comment out the last line everything work perfectly however with the
line in I get an error.

[quoted text, click to view]

Garth
9/28/2006 12:00:00 AM

Within the Test db there are only 15 row and as the query sit now with out
the datediff it will return 3 row all row are valid.

[quoted text, click to view]

Hugo Kornelis
9/29/2006 11:49:40 PM

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Garth,

In the first line (in the select), you are specifiying exactly how to
convert the string in HWEndDate0 to datetime - you specify a type of
120, which means that SQL Server assumes "yyyy-mm-dd hh:mi:ss".

In the last line (in the WHERE), you use HWEndDate0 in a function that
expects datetime or smalldatetime, so SQL Server will convert using
rules for implicit conversion. It appparently defaults to a format that
is not compatible with the "yyyy-mm-dd hh:mi:ss" format actually used.

Change this to
[quoted text, click to view]

As a longer term solution, I urge you to change the datatype of
HWEndDate0 to datetime or smalldatetime.

Also, on a final note:

[quoted text, click to view]

Getdate() is datetime. You are converting this to smalldatetime, using a
style parameter that is disregarded for conversion from datetime to
smalldatetime, then immediately convert it implicitly back by assigning
it to a variable declared as datetime. What exactly are you trying to
achieve here?

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn