Need Date out of DateTime in Text Field

Question

Post reply

Need Date out of DateTime in Text Field

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
July 2, 2009 at 3:54 pm

#131765

I need to pull the date only from a text field that contains the date and time in it. I tried using convert but it hasen't worked, so I tried to break it in sections as a new field but I can't get the year part right. Below is an example of what the value in the text field looks like and then what I'm trying to do.
7/2/2009 4:18:25 PM
7/2/2009 4:14:28 PM
selectCallLogID,
left(Date, charindex('/', Date)-1) + '/' +
substring(Date,charindex('/',Date)+1,charindex('/',Date)-1)
From#tmpDateConv
Gives me result: 12/26 which is the month/date; but I can't the year part down.
Any help is appreciated.
thx,
John

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

What is the exact result you want and is the source date a datetime column?

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 2

I want it as 7/2/2009.

the source field is a text field and the value is stored as 7/2/2009 11:07:23 AM

WayneS SSC Guru Points: 95460 More actions · Answer 3

latingntlman (7/2/2009)
I need to pull the date only from a text field that contains the date and time in it. I tried using convert but it hasen't worked, so I tried to break it in sections as a new field but I can't get the year part right. Below is an example of what the value in the text field looks like and then what I'm trying to do.
7/2/2009 4:18:25 PM
7/2/2009 4:14:28 PM
selectCallLogID,
left(Date, charindex('/', Date)-1) + '/' +
substring(Date,charindex('/',Date)+1,charindex('/',Date)-1)
From#tmpDateConv
Gives me result: 12/26 which is the month/date; but I can't the year part down.
Any help is appreciated.
thx,
John

So close...

select CallLogID,

left(Date, CharIndex(' ', Date)-1)

from #tmpDateConv

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]There are various options.

If you just want it as a string, you can use this (change the number on the end of the convert to vary the format):

select convert(varchar(10), getdate(), 120); -- ODBC format: yyyy-mm-dd

select convert(varchar(10), getdate(), 101); -- US format: mm/dd/yyyy

If you want a datetime value without the time component, use:

select dateadd(day, datediff(day, 0, getdate()), 0);

You're starting with a string that is in US date format (by the looks), so I'd combine them to get something like the following:

select dateadd(day, datediff(day, 0, convert(datetime, [MyDateTimeField], 101)), 0) as [MyDateField]

from dbo.MyTable;

[/font]

One word of advice: don't rely on implicit conversions between strings and dates. Your code will break if it moves between different default date formats.

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 5

Thanks guys,

Bruce, your suggestions "convert(varchar" won't work because it's a text field, so the field needs to be parsed to remove the spaces and time after the date. Then I can convert it.

Regards,

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

This should give you the idea

DECLARE @DateInString varchar(20)

SET @DateInString = '7/2/2009 4:18:25 PM'

SELECT CAST(@DateInString AS DATETIME)

SELECT DATEADD(dd,datediff(dd,0,@DateInString),0)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

latingntlman (7/3/2009)
Thanks guys,
Bruce, your suggestions "convert(varchar" won't work because it's a text field, so the field needs to be parsed to remove the spaces and time after the date. Then I can convert it.

Bruce's last query takes that into account. Did you try it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 8

Yeah, I tried Bruce's but it gave me a syntax error. Anyway, I went with Wayne's suggestion and it seems pretty simpler.