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.
| Author |
Topic |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2002-10-31 : 11:00:45
|
| Hello ,I have a stored procedure which has parameters as @CreationDateTimeFrom = '2002/10/08 23:00:00 and @CreationDateTimeTo = '2002/10/13 23:00:00 .If i enter the @creationDateTimeTo = 2002/10/12 23:00:00 , the query runs fine . But if i change to 2002/10/13 , the query fails giving an error as :'Error converting data type varchar to datetime '. I guess the problem is with the date format . The query looks like this : ---------select distinct i.Incident_GUID as 'GUID', i.[No] as 'NO', i.Status as 'STATUS', i.[Typ] as 'TYP', i.CreationUserID as 'CRTUSRID', i.DateTime as 'DATETIME', o.LastName as 'LN', e.Product as 'PROD', a.Country as 'CNTRY', v.RegistrationNumber as 'REGNO', c.CheckoutUserID as 'CHKOUT_USRI', c.CheckoutClub as 'CHKOUT_CLUB', c.CheckoutDateTime as 'CHKOUT_DAT', c.CheckoutWorkStation as 'CHKOUT_WS', o.FirstName as 'REASON', o.BirthDate as 'SIGNALFUPDATE' from Incident i left join Person o on i.Claimant_GUID = o.Subject_GUID left join Address a on i.IncidentLocation_GUID = a.Address_GUID left join Address d on o.HomeAddress_GUID = d.Address_GUID left join Entitlement e on i.Incident_GUID = e.Incident_GUID left join Vehicle v on i.Vehicle_GUID = v.Subject_GUID left join CheckoutInfo c on i.Incident_GUID = c.GUID where i.CreationDateTime >= "2002/10/08 23:00:00" AND i.CreationDateTime <= "2002/10/13 23:00:00"Then we execute this SP using exec statement with parameters as parameters as @CreationDateTimeFrom = '2002/10/08 23:00:00 and @CreationDateTimeTo = '2002/10/13 23:00:00 .and it fails .. Any ideas to this problem ???Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 11:08:31
|
| How about:select distinct i.Incident_GUID as 'GUID', i.[No] as 'NO', i.Status as 'STATUS', i.[Typ] as 'TYP', i.CreationUserID as 'CRTUSRID', i.DateTime as 'DATETIME', o.LastName as 'LN', e.Product as 'PROD', a.Country as 'CNTRY', v.RegistrationNumber as 'REGNO', c.CheckoutUserID as 'CHKOUT_USRI', c.CheckoutClub as 'CHKOUT_CLUB', c.CheckoutDateTime as 'CHKOUT_DAT', c.CheckoutWorkStation as 'CHKOUT_WS', o.FirstName as 'REASON', o.BirthDate as 'SIGNALFUPDATE' from Incident i left join Person o on i.Claimant_GUID = o.Subject_GUID left join Address a on i.IncidentLocation_GUID = a.Address_GUID left join Address d on o.HomeAddress_GUID = d.Address_GUID left join Entitlement e on i.Incident_GUID = e.Incident_GUID left join Vehicle v on i.Vehicle_GUID = v.Subject_GUID left join CheckoutInfo c on i.Incident_GUID = c.GUID where i.CreationDateTime BETWEEN '2002/10/08 23:00:00' AND '2002/10/13 23:00:00'Don't delimit date values with double quotes ("), use single quotes instead ('). If that doesn't work, you may need to format the date values in ISO format:select distinct i.Incident_GUID as 'GUID', i.[No] as 'NO', i.Status as 'STATUS', i.[Typ] as 'TYP', i.CreationUserID as 'CRTUSRID', i.DateTime as 'DATETIME', o.LastName as 'LN', e.Product as 'PROD', a.Country as 'CNTRY', v.RegistrationNumber as 'REGNO', c.CheckoutUserID as 'CHKOUT_USRI', c.CheckoutClub as 'CHKOUT_CLUB', c.CheckoutDateTime as 'CHKOUT_DAT', c.CheckoutWorkStation as 'CHKOUT_WS', o.FirstName as 'REASON', o.BirthDate as 'SIGNALFUPDATE' from Incident i left join Person o on i.Claimant_GUID = o.Subject_GUID left join Address a on i.IncidentLocation_GUID = a.Address_GUID left join Address d on o.HomeAddress_GUID = d.Address_GUID left join Entitlement e on i.Incident_GUID = e.Incident_GUID left join Vehicle v on i.Vehicle_GUID = v.Subject_GUID left join CheckoutInfo c on i.Incident_GUID = c.GUID where i.CreationDateTime BETWEEN '20021008 23:00:00' AND '20021013 23:00:00' |
 |
|
|
|
|
|
|
|
Error converting data type varchar to datetime .