Altering column fields with a Stored Procedure

[Milo Woodward]

I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String

sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDate, 4, 2)
sDay = Mid(sCurrentDate, 1, 2)
sYear = Mid(sCurrentDate, 7, 2)

If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).

The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it.

[Simon Hayes]

mw*******@quinnpumps.com (Milo Woodward) wrote in message news:<16**************************@posting.google. com>...

I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String

sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDate, 4, 2)
sDay = Mid(sCurrentDate, 1, 2)
sYear = Mid(sCurrentDate, 7, 2)

If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).

The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it.

I strongly suggest that you rethink your approach, and change the
column to datetime. You can then do date calculations using the
standard functions (DATEADD etc.), compare the values to datetime
variables without conversion, etc. You can use CONVERT() to extract
dates in a particular format for passing to other systems.

Using numeric will give you serious problems in the long run, although
I appreciate that you may have limited control over the data model.
But if you really have no option but to use numeric, then this should
work (assuming that as you said, all dates are 1950 or later):

update dbo.MyTable
set DateColumn = convert(char(8), convert(datetime, DateColumn, 3),
112)

alter table dbo.MyTable
alter column DateColumn numeric(8)

Simon