Problems with comparing date field with getdate()

[bj1]

Hi there!

I want to be able to compare my date field with getdate().
but i keep getting the error message:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

My date field is called 'starttime' and looks like:

13/09/02 11:21:38

getdate() looks like:

2002-11-15 17:02:26.703

I am only interested in the actual date part not the time.

Can someone help me?

Much appreciated
B

 

[richardtsa]

HELLO


use the convert function

It looks like this


convert(char(10),getdate(),112)

if you look in the help you will see diff no(112) that show diff date formats

 

[r937]

quote: "getdate() looks like: 2002-11-15 17:02:26.703"

actually, no -- it only looks like that when you display it, and if you don't say what format to display it in, then it is displayed in a default format

datetimes are actually stored internally as integers

as for your problem, converting getdate() to character so that it can be compared to your character date field is probably the easiest option, and richardtsa's suggestion is fine

a better option, albeit more difficult once the table has been created and the app built, is to use a datetime field for dates

that way you can do date arithmetic too, like finding all dates within x days of today


rudy

 

[MeanGreen]

If your starttime field is already defined as datetime, you should not have to worry about what getdate() looks like. Internally they are the same. What you want to do is use the DATE functions to compare the two fields. Example:

select * from mytable
where datediff(dd,starttime,getdate()) > 5

This will get all rows where starttime is older then the last 5 days.

Hope this helps.

 

[bj1]

By using the above query provided by MeanGreen:

select * from mytable
where datediff(dd,starttime,getdate()) > 5

i get this error message:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I am continually getting this same error message for every query i try.

Any suggestions?

 

[r937]

well, you originally said you just wanted to compare to getdate(), so converting getdate() to a character string the same format as your character string date field is the way to go

if, however, you do intend to do date arithmetic, then you should change your table, make your date field an actual datetime datatype

if it's not your table, and/or you can't change it, then you are in deeper trouble than you think


rudy

 

[bj1]

Hi

This is the query i am using:

select *
from callreports
where starttime = convert(char(10),(getdate()),112)

It compiles with no errors but all i get is the column headings. Is this what i should be doing?
Does this mean that i won't be able to to say run daily reports where getdate() -1 will equal yesterday??

 

[r937]

what is the datatype of starttime?

can you show the table layout, or the create table statement for your table?


rudy

 

[bj1]

This is all i have:

starttime (varchar(50), Null)

This query:

select starttime
from callreports

Outputs starttime in this format:

13/09/02 11:21:38

Is this enough info?
Thanks you for your help...
B

 

[r937]

okay, now we can explain

your varchar field does not contain a date, it contains a string

if you want to check it against getdate(), you have to

1) convert getdate() to a similarly formatted string

2) disregard the time portions

the reason for 2) is because you are never going to match a time in your database to the current time portion of getdate() -- and if you do, it'll be a blooming miracle

okay, so what you want is the first 8 characters of your starttime string, which can be got using the LEFT function, compared to the same characters of getdate, which can be got by judicious use of CONVERT --

[tt]where left(starttime,8)
= convert(char(8),getdate(),3)[/tt]

notice you ahve to use convert style 3 to get dd/mm/yy

rudy

 

[bj1]

Hey Rudy

Thats done it...
Thanks so much for your help.

B

 

[MeanGreen]

You could also use convert to make your starttime a datetime type. Here is that solution:

select * from mytable
where datediff(dd,convert(datetime,starttime,101),getdate()) > 5

Hope this helps.

 

[bj1]

when i try to make starttime a datetime i get this error message:

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

Any suggestions??

 

[MeanGreen]

Try this:

select * from mytable
where datediff(dd,convert(datetime,left(starttime,8),101),getdate()) > 5

Hope this helps.

 

[r937]

meangreen, style 101 is mm/dd/yyyy, bj1's string is definitely style 3

but anyhow, does convert really pay attention to the style when converting *to* a datetime? i thought the style was used only when deciding which string format the convert *from* a datetime

bj1's error is likely due to a bad string, empty string, invalid value in the string (e.g. feb 30), etc.

bj1, if you have to do any date calculation at all (e.g. today minus one day), then please, save yourself future grief, run an ALTER TABLE statement and change your starttime column to datetime datatype

this will probably involve fixing the very same values that are causing you on-the-fly conversion errors in the query now

rudy

 

[MeanGreen]

If you decide to convert your column to datetime, you could use the following query to identify your invalid datetime rows:

select * from mytable
where isdate(starttime) = 0

Hope this helps.

 

[tlbroadbent]

Rudy,

The style number is a valid parameter when converting from character string to datetime.

Convert(datetime, starttime, 3) will convert a British/French style date string to SQL datetime. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[r937]

thanks, terry

i don't have sql/server, or i would test these things out myself

tell me, if the style is supplied, does that mean that values which would otherwise be valid dates will get rejected?

in other words, does

[tt] convert(datetime,'2002-11-18',3)[/tt]

throw an error, and if not, why not?

however, i can see where it might be useful, where adding the style would override sql/server's default in cases where it's ambiguous, e.g. 03/04/02


rudy

 

[tlbroadbent]

Rudy,

If the date string doesn't match the supplied style, SQL will throw an error or produce an incorrect result if the date is ambiguous. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bj1]

Thanks heaps for everyone's input on this one...
It always seems to be the smallest of problems that cause the most grief...
I have solved my problem for the time being but will run an ALTER TABLE statement and change my starttime column to datetime datatype...
In the last day or so i have learnt heaps about converting and the different types of formats there are.
I think this is why this site is such a success...
Thanks again,
B