How To Convert Datetime Into Varchar

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 2000 Forums

SQL Server Development (2000)

How To Convert Datetime Into Varchar

Author

Topic

murtzzz
Starting Member

14 Posts

Posted - 2007-07-09 : 07:56:19

Hi..

Im trying to convert a column that has a 'datetime' value stored in it into a varchar so I can set conditions in my sql statement.

Here is the code I want to use...

WHERE (contact_event.event_date >= '01/01/2007' and contact_event.event_date < '01/04/2007'

When I use that code, I get this message...

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

Please help! I want to somehow convert the date value to a text value so it can select the data I want between those dates!

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 08:04:22

specify the date in ISO format YYYYMMDD and you will be fine


contact_event.event_date >= '20070101' and contact_event.event_date < '20070104'

KH
[spoiler]Time is always against us[/spoiler]

murtzzz
Starting Member

14 Posts

Posted - 2007-07-09 : 08:10:44

Thing is though mate.. the date in the column comes up as this..

2007-06-01 00:00:00.000

and Ive tried doing this..

contact_event.event_date >= '2007-06-01 00:00:00.000'

but it gives me the same error message!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 08:12:35

what is the data type for column event_date ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 08:13:29

Also try posting your full query. Are you sure the error comes from that section of code ?

KH
[spoiler]Time is always against us[/spoiler]

murtzzz
Starting Member

14 Posts

Posted - 2007-07-09 : 08:19:46

Heres the full code

SELECT contact_event.event_id AS ID, warehouse_reference.dbo.org__gp.gp_code AS [Current GP code], external_staff.sec_prac

FROM ((contact_event INNER JOIN patient ON contact_event.patient_id = patient.patient_id)
INNER JOIN external_staff ON (patient.gp_code = external_staff.gp_code)
AND (contact_event.practice_code = external_staff.sec_prac))
INNER JOIN warehouse_reference.dbo.org__gp ON (external_staff.central_code = warehouse_reference.dbo.org__gp.gp_code)
AND (external_staff.sec_prac = warehouse_reference.dbo.org__gp.practice_code)

WHERE (contact_event.event_date >= '2007-06-01 00:00:00.000' and contact_event.event_date < '2007-06-31 00:00:00.000'
and ((contact_event.event_type_code)='mc') AND ((warehouse_reference.dbo.org__gp.Close_Date) Is Null));

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 08:27:07

quote:
Originally posted by murtzzz

Thing is though mate.. the date in the column comes up as this..

2007-06-01 00:00:00.000

and Ive tried doing this..

contact_event.event_date >= '2007-06-01 00:00:00.000'

but it gives me the same error message!

Assuming that the column event_date is a datetime data type, you don't worry about the format 2007-06-01 00:00:00.000. It is just how Query Analyser present the date time to you.

Are you sure it is still giving you the same error message ? Irregardless of the data type for event_date is varchar or datetime, the following 2 statement should not give you any error.

contact_event.event_date >= '2007-06-01 00:00:00.000'
contact_event.event_date >= '20070601'

KH
[spoiler]Time is always against us[/spoiler]

murtzzz
Starting Member

14 Posts

Posted - 2007-07-09 : 09:04:47

you see the thing is.. the dates I am specifying may not be an exact match to the columns.. I want data BETWEEN the 1st of june and the 31st of june.. and the columns may not have the dates '01/06/07' and 31/06/07' in them

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 09:09:02

quote:
Originally posted by murtzzz

you see the thing is.. the dates I am specifying may not be an exact match to the columns.. I want data BETWEEN the 1st of june and the 31st of june.. and the columns may not have the dates '01/06/07' and 31/06/07' in them

So this is root of the problem. Is there a 31st in June ?

KH
[spoiler]Time is always against us[/spoiler]

murtzzz
Starting Member

14 Posts

Posted - 2007-07-09 : 09:24:11

That shouldnt matter should it?.. I want data 'between' those numbers

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-09 : 09:27:43

of what datatype is your event_date column?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 09:28:36

it does matter. event_date is datetime datatype, SQL Server will attemp to convert the string to datetime before comparing. It will have problem converting '31/06/07'

You can specify it as


event_date >= '20070601' and 
event_date < '20070701'

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-09 : 09:51:19

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources