This also doesn't handle validation (like the other answer, it will choke on values like 252525). SELECT may need to do things a little differently if you could have 250999 etc, then you would need some way to indicate whether that's 1999 or 2099, for example. Likewise when displaying the DATE as you desire, you need to use STYLE 3Īnother approach is to cast it directly to a date in SQL Server 2008 or above, then store it that way as commented above. (I am sure that there are other workarounds and conversion styles that would work as well.) To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. The results of the last two selects are: DefaultFormat Select the value in default Year-Month-Day SET = Second: Convert using STYLE 3 to get DD/MM/YY interpretation Convert your undelimited string DDMMYY into a DATE Using the CONVERT style option 3, you can do the following: DECLARE VARCHAR(10) I changed the month to 8 to make it easier to double check. Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around. (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.) An undelimited string of integers is assumed to be Year-Month-Day order. I understand your problem to be how to successfully convert the string into a DATE value.
0 Comments
Leave a Reply. |