SQL fn (convert(.......,............,) as xxx

Question

SQL fn (convert(.......,............,) as xxx

Aziz-802502

SSC Enthusiast

Points: 198
More actions
December 5, 2008 at 5:00 am

#196699

I am trying to pick up date of birth using the SQL:
SELECT * FROM openquery (mmm,'SELECT DISTINCT
................
,{fn convert(p.birth_date,SQL_TIMESTAMP)}AS DOB
........................
FROM
nnn p
')
But outcome is not ok. Original date of birth is 1949. But this sql gives 2049. I trying with convert(datetime, p.birth_date, 101) which does not work here (error)-openquery.
Original DOB: 1949-04-21 00:00:00.000
SQL outcome: 2049-04-21 00:00:00.000
I don't know why? Any help for me? Please

Viewing 5 posts - 1 through 4 (of 4 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You seem to be running into the "2-digit year cutoff" property.

Your best solution might be to simply make sure that the remote system actually stores and passes 4 digit years and not 2, so that SQL doesn't have to guess which century to use for it. This would be the whole 2K bug all over again.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Aziz-802502 SSC Enthusiast Points: 198 More actions · Answer 2

Some date with 19?? are working properly. Please details. I am not so expert.

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

From Books Online 😉

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

exec sp_configure 'show advanced options','1'

reconfigure

go

/* show settings */

exec sp_configure 'two digit year cutoff'

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

What is the datatype of p.birth_date? Can you provide a few sample values?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden