CODEWITHSUNDEEP
sql-server-2005
sclass
sclass

Reputation: 1

2005 SQL Server Management Studio

Can someone interpret this query. I am aware that alias, conversion, and linked databases are involved, but not familiar placement of numbers "4,2".

set birthday = convert(datetime, left(a.dob,2)+ '/'+ 
               right(left(a.dob,4),2)+'/'+ 
               right(a.dob,2)+left(right(a.dob,4),2)) 
from 
  [plkfsql2k5\prod2k5].st_data.dbo.st_patient a

Upvotes: 0

Views: 30

Answers (1)

JNevill
JNevill

Reputation: 50308

This left(a.dob,2)+ '/'+right(left(a.dob,4),2)+'/'+right(a.dob,2)+left(right(a.dob,4),2) nightmare of a substring concatenation is taking a date, probably stored as text (ugh) and returning it in a format that can be converted to a date with the convert(datetime, <textdate) formula it's wrapped in.

The text date appears to be in a format where the month is in the first 2 characters, the day is in the 3 and 4th characters and the year... well the years is stored instead of 1999 like 9919 because some psychopath designed this field...

So it takes this mmddyyYY format (that only a crazy person would use) and coverts it to mm/dd/YYyy and the n uses the convert(datetime, <text date>) function to make it into an actual date.

Note that if you are in a country that uses dd/mm/yyyy format, then my explaination may need to be tweaked as the incoming text value may be ddmmyyYY and converts it to dd/mm/YYyy. Still a crazy-banana's starting point though.

Upvotes: 1

Related Questions