August 16, 2011 at 12:32 pm
I'm collecting a string date in the format of:
"08-20-2011"
However, it will be stored in an INT field and compared against other dates and needs to be in the following format:
"20110820"
Coverting and string functions are always a bit out of my element...
How would I go about doing that?
Thanks for any assistance!
August 16, 2011 at 1:00 pm
The best (and only way imho) is to forget the date to int thing and put your date in a datetime field. You wouldn't put a character in an int field would you? Put datetime data in datetime fields. You say you are going to compare them to other dates. Are you going to convert your string to an int so that before you can do your comparisons you have to convert your int back to a string so you can convert to a date?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 1:16 pm
You bring up a good point, which I totally overlooked, not reviewing how I manipulate the dates in the rest of the code. I do indeed have to convert to smalldatetime to do the compares so I might as well just convert to smalldatetime initially so I don't have to do it again later:
SELECT CAST(RIGHT(ad.LDescription,10) AS smalldatetime) AS NewDate
Thank you for pointing out the obvious.
😉
August 16, 2011 at 1:20 pm
No problem. It is always best to store your data in the correct datatype for the value. Makes life so much easier down the road. Sorry if I sounded snippy but I just went through this exact same thing today with a colleague.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 1:26 pm
August 16, 2011 at 1:54 pm
Another good point. Thanks for all the help.
Question though, if this column is in Date format and the other fields are smalldatetime and I do a DATEDIFF between the two, will I encounter any issues?
August 16, 2011 at 2:01 pm
tacy.highland (8/16/2011)
Another good point. Thanks for all the help.Question though, if this column is in Date format and the other fields are smalldatetime and I do a DATEDIFF between the two, will I encounter any issues?
Depends on what you mean by issues. You can perform datediff with those two datatypes without any system errors. There CAN be logic errors however. In effect a Date datatype is like a datetime with the timestamp at all 0s.
Play around with to see how it works.
declare @DateTime as datetime
select @DateTime = GETDATE()
declare @Date as date = '2/16/2010'
select @DateTime, @Date
, DATEDIFF(y, @DateTime, @Date)
As long as you understand the intervals you are comparing with you should be fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 2:10 pm
Makes sense.
Thank you! I appreciate the assistance!
August 16, 2011 at 4:50 pm
tacy.highland (8/16/2011)
Another good point. Thanks for all the help.Question though, if this column is in Date format and the other fields are smalldatetime and I do a DATEDIFF between the two, will I encounter any issues?
It's ALWAYS (don't get to say that often 😀 ) a much better idea to make sure the datatypes are exact matches. That way you never have to worry about any type of implicit data conversion causing and index scan instead of an index seek. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply