datediff mm between a timestamp in varchar(50) and a small datetime ???

Question

Post reply

datediff mm between a timestamp in varchar(50) and a small datetime ???

JPLeBlanc

Old Hand

Points: 365
More actions
January 20, 2009 at 10:16 am

#200139

Here is what i have
Timestamp - nvarchar(50)
hire date - smalldatetime
Timestamp [Hire Date]
20081013 07:22:48.000 8/17/2000 98 months
20081013 07:32:06.000 1/10/2003
20081013 07:51:06.000 7/15/1989
I would like to show the months between the two dates, but the problem is that the timestamp field is not in a date format.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Looks like you have everything you need. Here is my test code based on your post.

create table #TestTable (

DateStamp nvarchar(50),

HireDate smalldatetime

);

insert into #TestTable

select N'20081013 07:22:48.000','2000/08/17' union all

select N'20081013 07:32:06.000','2003/01/10' union all

select N'20081013 07:51:06.000','1989/07/15';

select DateStamp, HireDate, datediff(mm, HireDate, DateStamp) from #TestTable;

drop table #TestTable;

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Lynn gave you the solution.

However for the query I would suggest to use a convert with a specific conversion type.

This way everybody maintaining your query will know of the explicit conversion.

select DateStamp, HireDate, datediff(mm, HireDate, convert(smalldatetime, DateStamp, 121 ))

from #TestTable;

I would alter the column "timestamp" to a datetime datatype column !

Presentation is a front end problem !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

JPLeBlanc Old Hand Points: 365 More actions · Answer 3

Thank you both for the solution, I may not have included enough information.

both fields are in the same table with 64k records and i am creating a view for this data within MSSQl management studio. since both fields exist in the table i would like to create a new field since having both old fields and the new field will be pertinent to the viewer.

what i am having issues with is the conversion of the timestamp field to be able to do the datediff function, here is what i plugged in, and unfortunately i am still getting a conversion error.

DATEDIFF(mm, dbo.[PCS Data - Details].[Hire Date], CONVERT (smalldatetime, dbo.[PCS Data - Details].Timestamp, 121)) as [Rep Tenure]

Here is the error i am receiving

error message: Conversion failed when converting character string to smalldatetime data type.

thank you both for your help thus far and my appologies for not presenting it correctly i hope i have it right this time 🙂

GSquared SSC Guru Points: 260824 More actions · Answer 4

The problem isn't directly the code in the query. It's that there's data in the timestamp field that SQL can't convert to a date.

If you select from that table, Where IsDate(Timestamp) = 0, it will show you the rows where the timestamp isn't a valid date. What you do with those rows is going to depend on your particular situation.

If you can't clean them up, then you'll need to add another step to your query where you select only the rows where IsDate(Timestamp) = 1. Because of the way SQL handles Where clauses, most likely you'll need to either use that in a Case statement in the Select part of your query, or you'll need to dump the data into a temp table with just the rows that can be converted, and then query the temp table with the DateDiff calculation.

Examples:

select

case IsDate(Timestamp)

when 1 then datediff(month, DateHired, Timestamp)

else null

end as Months

from ...

or

select DateHired, Timestamp

into #TempTable

from dbo.MyTable

where IsDate(Timestamp) = 1

select datediff(month, DateHired, Timestamp)

from #TempTable

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JPLeBlanc Old Hand Points: 365 More actions · Answer 5

WOOT! thanks to both of you for your assistance!

here is what i am using.

I dropped the original timestamp from the view and replaced with this.

CASE WHEN IsDate(dbo.[PCS Data - Details].Timestamp) = 1 THEN CONVERT (smalldatetime , timestamp) ELSE NULL END as [contact date]

Here is the code that gives me tenure by month.

CASE IsDate(Timestamp) WHEN 1 THEN datediff(mm , [hire date] , Timestamp) ELSE NULL END as [Rep Tenure]

Lynn, looking at these and hat worked i beleive it is due to the fact that not all of the timestamps were in fact readable as dates so they wouldn't convert and thus error out. thats another trick i will have to search more for in the future. but you guys have definately helped me complete this project, and for that i am truly appreciative!

GSquared SSC Guru Points: 260824 More actions · Answer 6

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

As you have seen, if the question is good, the solution(s) come in quickly :w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

JPLeBlanc Old Hand Points: 365 More actions · Answer 8

Hey guys, update to this question... blech so many changes to my data. 🙁

[original service date]

20070630

20070804

20080711

20030124

20081213

20020103

essentially i would like to do the same as in the above posts, compare it to get a tenure in months against [contact date]. [contact date] is in smalldatetime datatype format. So going along with what i learned earlier and knowing that this is a new field added to my data, i am going to have some blanks in most of the records.

had this been a typical date time field i would write

datediff(mm,[original service date],[contact date])

but of course this wont work since [original service date] is a string

I was also using the code snippet from previous posts but it does not work as far as converting the string to a small date time.

CASE WHEN IsDate([original service date]) = 1 THEN CONVERT (smalldatetime , [original service date]) ELSE NULL END

Anyone got any good ideas?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

JPLeBlanc,

Just making sure that you know what you're getting...How many months would you say the following should give?

StartDate EndDate

20080101 20080229

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JPLeBlanc Old Hand Points: 365 More actions · Answer 10

Jeff,

I beleive that would be two months since it is calculating it in days correct?

so not just 1 calendar months difference. or am i backward? 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Wrong, 1 month. This will also return 1: datediff(mm,'2009-01-31','2009-02-01').

Peso wrote an excellent article on datediff. You can find it on SQLTeam.com.

JPLeBlanc Old Hand Points: 365 More actions · Answer 12

I have read up a bit on that article as well as some others regarding conversions of varchars to date time, but i am still missing a peice of the puzzle for the conversion itself.

I did find an issue concerning the conversion of the field and it looks like i may have some trash values in the field. as seen below.

00111

20060613

20081007

15911

03211

.

20020513

I found this article concerning conversions, but i cant seem to extract what i really need from my problem. http://www.karaszi.com/SQLServer/info_datetime.asp

pulling from all of the code snippets i have found and have been graciously given here i have put this together but am still not able to get a conversion.

CASE WHEN isdate(CONVERT (smalldatetime , [original service date])) = 1 THEN CONVERT (smalldatetime , [original service date]) ELSE NULL END

I am really sorry for being a pest here, but thank you all for your assistance.

GSquared SSC Guru Points: 260824 More actions · Answer 13

Get rid of the conversion inside the IsDate. That'll give you errors.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JPLeBlanc Old Hand Points: 365 More actions · Answer 14

JPLeBlanc

Old Hand

Points: 365

January 22, 2009 at 10:44 am

#931311

Thank you GSquared this was what i needed!