Reading nVarChar date in reverse and store in DateTime column

Question

Reading nVarChar date in reverse and store in DateTime column

@RDBMS

Right there with Babe

Points: 757
More actions
September 24, 2011 at 9:28 am

#146352

A table has dates stored as nVarChar in the format: dd/mm/yyyy. I have added a new DateTime column and want to store the nVarChar dates into this column for all the rows.
I used: SET DATEFORMAT ymd; to store all dates, internally, in this globally accepted format. I want to know:
(1) How to read OldDateColumn in reverse in SQL Query itself, and
(2) How to update the NewDateColumn with the old date in format: yyyy/mm/dd?
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

@RDBMS (9/24/2011)
A table has dates stored as nVarChar in the format: dd/mm/yyyy. I have added a new DateTime column and want to store the nVarChar dates into this column for all the rows.
I used: SET DATEFORMAT ymd; to store all dates, internally, in this globally accepted format. I want to know:
(1) How to read OldDateColumn in reverse in SQL Query itself, and
(2) How to update the NewDateColumn with the old date in format: yyyy/mm/dd?

SET DATEFORMAT does not cause SQL Server to store dates in a specific format. From BOL (emphasis mine):

Sets the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings.

Dates are stored as numeric data internally in SQL Server. I'd recommend just doing an update statement setting the value of the NewDateColumn to the OldDateColumn. As long as all those date strings are valid for the DateTime data type, then you should be all set.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

Also you could use the "yyyymmdd" format without any "-" or "/" characters and they'll be accepted with any settings. It's a safe bet.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA