Find Date String 1

[IanWaterman]

I need to create some SQL to find a specific notes and date note was entered.

Dataset looks like

010127 5 #Incident Manager
010127 6 Incident Reviewed - Confirmation that user is happy should be sought
010127 7 Fred Bloggs--------------------05/07/10-@-12:55:09--------

Each line of the Notes has an individual line number but no date field

I need to find the notes which start with ~Incident and then find the entry date
to output

UF01027 #Incident 05/07/2010

Notes can be as short as the above to several lines long.

so far I have got

select * from callnotes
where callno = '010127'
and (notes like '%#Incident%' or notes like '%---%/%')

Thank you

Ian

 

[IanWaterman]

I am using SQL 2008

 

[IanWaterman]

Got this far

select callno, notes, substring(notes, patindex('%/%/%', notes)-2, 8) from callnotes
where callno = 'UF010127'
and line_no = 7

This returns my date as a string '05/07/10'

How do I convert this to British date format 05/07/2010 or
datetime 05/07/2010 00:00:00

Ian

 

[IanWaterman]

I have tried using

convert(nvarchar(10), substring(notes, patindex('%/%/%', notes)-2, 8), 3)

But it does not recognise

25/06/10 as a date

and converts 05/07/10 to 7th May 2010 when in British format its 7th July 2010.

Ian

 

[gmmastros]

A minor change should do it.

[tt]convert(nvarchar(10), substring(notes, patindex('%/%/%', notes)-2, 8), [!]3[/!])[/tt]

The 3rd argument to the convert function identifies the style. 3 = British/French. Try using 1 instead while is U.S..



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

The date string is stored as

05/07/10
16/06/10
which is British.

I have tried running query with 1 but it does exactly the same

05/07/10 still comes out as May 7th and
26/06/10 is not recognised as a date.

Ian

 

[IanWaterman]

I have even tried 2 which is ANSI

yy.mm.dd

and it still runs and brings back same dates

I would have thought it would fail as string is not in that format.

Ian

 

[gmmastros]

Sorry. I was confused.

When I run this...

Select Convert(DateTime, '05/07/10',3)

I get... 2010-07-05 00:00:00.000 (July 5, 2010).

I think the problem might be that you are converting to nvarchar(10). Try converting to datetime instead.

convert([!]datetime[/!], substring(notes, patindex('%/%/%', notes)-2, 8), 3)

I think the 3rd argument to the convert function only applies to dates and money (but I could be wrong).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

Sorry I confused you ;-)

When I use datetime it works fine.

Here is my final query

select callno, min(line_no) line_no, 
min(case when notes like '%#Incident%' then 
convert(datetime, '31/12/2100', 103) else
 convert(datetime, substring(notes, patindex('%/%/%', notes)-2, 8), 3) end)as notedate 
from callnotes cd
where (notes like '%#Incident%' or notes like '%---%/%')
and line_no >= (select min(line_no) from callnotes ci
				where ci.callno = cd.callno
				and notes like '%#Incident%'
				Group by callno)
Group by callno

 

[gmmastros]

I was thinking about this a little more, and I'm a little concerned about something.

Since you are getting a US formatted date, you must be causing an implicit conversion to datetime somewhere AND your default language must be us_english (or another language that uses mm/dd/yyyy).

-- Set language to U.S. English
Set Language us_english

-- Declare a DateTime variable
Declare @d datetime

-- convert to datetime with style = british
Set @d = Convert(datetime, '05/07/10',3)
-- Show date (returns Jul 5 2010)
Select Convert(varchar(30), @d)

-- Convert to nvarchar with style = british
-- The next line causes an implicit convert to datetime
Set @d = Convert(nvarchar(10), '05/07/10',3)
-- Show date, returns (May 7 2010)
Select Convert(varchar(30), @d)

Run the code again, but with the language set to british.

-- Set language to U.S. English
Set Language [!]british[/!]

-- Declare a DateTime variable
Declare @d datetime

-- convert to datetime with style = british
Set @d = Convert(datetime, '05/07/10',3)
-- Show date (returns Jul 5 2010)
Select Convert(varchar(30), @d)

-- Convert to nvarchar with style = british
-- The next line causes an implicit convert to datetime
Set @d = Convert(nvarchar(10), '05/07/10',3)
-- Show date, returns (Jul 5 2010)
Select Convert(varchar(30), @d)

Notice that both times, the string is converted to a date that is Jul 5 (not May 7).

The reason I am concerned is that you appear to prefer british formatted dates, but your language setting appears to be us_english. This could have other unintended consequences. I encourage you to read this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

It shows you how to detect the default language (that affect new logins), how to determine a login's language, and also how to change a login's language.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Here's where you get the implicit datetime conversion:

[tt]
case when notes like '%#Incident%'
then convert(datetime, '31/12/2100', 103)
else convert([!]nvarchar(10)[/!], substring(notes, patindex('%/%/%', notes)-2, 8), 3) end
[/tt]

When SQL Server evaluates a Case/When statement, it looks at all branches of execution and determines the resulting data type based on [google]sql server's data type precedence[/google]. In this case, datetime has the higher precedence, so the else condition was getting converted to datetime (implicitly). I also have a blog that explains this in further detail.

http://blogs.lessthandot.com/index....erver/sql-server-case-when-data-type-problems

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom