July 16, 2009 at 9:35 am
I have a field called birthdate that inserted from a web application to the table.
The data type is varchar(50). data like 3/15/1991, or 08/20/1991.
I have a view created from the table for reporting purpose.
The view is something like below, I use student first+last+birthdate to identify the student. (for this one, we don't use a student ID for our reason), so the birthdate is a very import field, student may enter the date in two ways, one is m/d/yyyy, the other is mm/dd/yyyy, data like:3/15/1991,or 08/20/1991.
How can I make them all formated like m/d/yyyy?
Thanks
July 16, 2009 at 9:49 am
First, I'll give you the blanket warning that storing dates as varchars is just plain bad practice. I'm sure, just like anyone else, you're working with a design that someone else came up with. I would recommend changing it to a datetime, but that's your call.
DECLARE @BirthDate varchar(50)
SET @BirthDate = '07/16/2009'
SELECT CASE WHEN LEFT(@Birthdate,1) = '0' THEN SUBSTRING(@BirthDate,2,LEN(@BirthDate)) ELSE @BirthDate END as BirthDate
July 16, 2009 at 10:11 am
Thank you very much, will change to small date time to store the birthdate.
July 16, 2009 at 10:19 am
You might be able to just do a CAST on your values and convert them to smalldatetime that way. EG:
Output:
03/25/1991
Mar 25 1991 12:00AM
VarCharDateSmallDateTimeDate
3/5/19911991-03-05 00:00:00
DECLARE @Date VARCHAR(200)
DECLARE @FixedDate SMALLDATETIME
SET @Date = '3/5/1991'
SET @FixedDate = CAST(@Date AS SMALLDATETIME)
PRINT @Date
PRINT @FixedDate
SELECT @Date AS VarCharDate, @FixedDate AS SmallDateTimeDate
Then if you need to do any operations, since the field is smalldatetime, you can do them much easier in the future.
July 16, 2009 at 10:32 am
Thank you very much, I will try that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply