quote:

---

Originally posted by RalphWiggum

Hi all. I have a strange one.
First of all, I have a stored procedure that works fine with the exception of needing to allow datetime parameters to look at a varchar field. I feel like this should be possible but I can't get it to work.

1st. The field is VARCHAR(40), with the last 26 characters containing a datetime format of 2012-01-01-18.49.15.032000. The stupid thing about this field is that it also contains a username, so the entire field will actually contain data like USERNAME 2012-01-01-18.49.15.032000

I need the stored procedure's datetime parameters to look at the datetime portion of this varchar field. I have concluded that SQL Server hates the - between the date and time, or between the 01 and 18 in this example. It also seems to hate the .xxxxxx milisecond format, and prefers the .xxx

So, in order to attempt to parse it out, I have the following where clause in my SP to attempt to look at the last 26 characters of this goofy field, and I've also attempted to parse the datetime portion of this field into something SQL Server likes. Here is what I have:

WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, CAST(LEFT(RIGHT(RTRIM(W.USER3), 26),10) + ' ' + replace(LEFT(RIGHT(RTRIM(W.USER3), 15),9), '.', ':') + LEFT(RIGHT(RTRIM(W.USER3), 6),3)AS DATETIME))) BETWEEN @StartDate AND @EndDate

This looks and parses okay in SSMS, however, when I exeute the SP, I get "Conversion failed when converting datetime from character string".

Any ideas would be greatly and awesomely appreciated.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by RalphWiggum

Hi all. I have a strange one.
First of all, I have a stored procedure that works fine with the exception of needing to allow datetime parameters to look at a varchar field. I feel like this should be possible but I can't get it to work.

1st. The field is VARCHAR(40), with the last 26 characters containing a datetime format of 2012-01-01-18.49.15.032000. The stupid thing about this field is that it also contains a username, so the entire field will actually contain data like USERNAME 2012-01-01-18.49.15.032000

I need the stored procedure's datetime parameters to look at the datetime portion of this varchar field. I have concluded that SQL Server hates the - between the date and time, or between the 01 and 18 in this example. It also seems to hate the .xxxxxx milisecond format, and prefers the .xxx

So, in order to attempt to parse it out, I have the following where clause in my SP to attempt to look at the last 26 characters of this goofy field, and I've also attempted to parse the datetime portion of this field into something SQL Server likes. Here is what I have:

WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, CAST(LEFT(RIGHT(RTRIM(W.USER3), 26),10) + ' ' + replace(LEFT(RIGHT(RTRIM(W.USER3), 15),9), '.', ':') + LEFT(RIGHT(RTRIM(W.USER3), 6),3)AS DATETIME))) BETWEEN @StartDate AND @EndDate

This looks and parses okay in SSMS, however, when I exeute the SP, I get "Conversion failed when converting datetime from character string".

Any ideas would be greatly and awesomely appreciated.

---

thats the problem of mixing datatypes. why not maintain date values independently in a datetime field?
Otherwise you need to put some parsing logic like suggestions given and also make sure date values are in consistent unambiguos format
make sure you read this

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---