Create a procedure to change date value of a column in a table ? (SQL)
-
Hi everyone, I have a table with the name is: Detail, that includes 'DateOfBirth' column (datetime type). For example: 1986-06-16 00:00:00.000 Now I want to create a store procedure to swap "day" value and "month" value for date values that have "day" and "month" value smaller 13, such as : Origin value: 1984-05-10 (10th May) Expected value: 1984-10-05 (5th Oct) Do you know how can do that ? Thanks and regards, Tai
-
Hi everyone, I have a table with the name is: Detail, that includes 'DateOfBirth' column (datetime type). For example: 1986-06-16 00:00:00.000 Now I want to create a store procedure to swap "day" value and "month" value for date values that have "day" and "month" value smaller 13, such as : Origin value: 1984-05-10 (10th May) Expected value: 1984-10-05 (5th Oct) Do you know how can do that ? Thanks and regards, Tai
This is not tested and it is not the only way to do this:
UPDATE MYTABLE
SET DOB = CAST(CAST(YEAR(DOB) AS VARCHAR(4)) +
CAST(DAY(DOB) AS VARCHAR(2)) +
CAST(MONTH(DOB)AS VARCHAR(2)) AS DATETIME) +
CAST(DOB AS TIME) -
Hi everyone, I have a table with the name is: Detail, that includes 'DateOfBirth' column (datetime type). For example: 1986-06-16 00:00:00.000 Now I want to create a store procedure to swap "day" value and "month" value for date values that have "day" and "month" value smaller 13, such as : Origin value: 1984-05-10 (10th May) Expected value: 1984-10-05 (5th Oct) Do you know how can do that ? Thanks and regards, Tai
taibc wrote:
Do you know how can do that ?
Using string-manipulation functions. The problem here is not the format of your date, but the fact that it's stored as a string, not as a date. What are you going to do with the date "6 may"? Would that become "5 juli", or are you going to "hope" that it is the correct date (that's what you state; you'd like to skip those)?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
taibc wrote:
Do you know how can do that ?
Using string-manipulation functions. The problem here is not the format of your date, but the fact that it's stored as a string, not as a date. What are you going to do with the date "6 may"? Would that become "5 juli", or are you going to "hope" that it is the correct date (that's what you state; you'd like to skip those)?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
This is not tested and it is not the only way to do this:
UPDATE MYTABLE
SET DOB = CAST(CAST(YEAR(DOB) AS VARCHAR(4)) +
CAST(DAY(DOB) AS VARCHAR(2)) +
CAST(MONTH(DOB)AS VARCHAR(2)) AS DATETIME) +
CAST(DOB AS TIME) -
Thank you. Yes, my expected result is 5 June. I am not good with fuctions in sql, do you have any ideas ? Kind regards,
taibc wrote:
Yes, my expected result is 5 June.
That's not what you stated in your original question; take a look, you wanted only those where you could "invalidate" the date by having a "day" larger then the number of months. 5 may would be skipped if you do it like that.
taibc wrote:
I am not good with fuctions in sql, do you have any ideas ?
Convert it to a date-column, as it should have been. It's either that, or trying your luck at string-manipulation. You'll have to write the code yourself btw, I'm not going to.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Thank you. I got an error when trying your code: "Type TIME is not a defined system type" I am using Microsoft SQL Server
I use TIME with SQL2008. You can use the CONVERT command to give you the time portion of the DATETIME column as a VARCHAR. (Look at 108)