April 9, 2013 at 10:07 am
I've been searching for something similar, but haven't quite found it. My dilemma is that this table was created with a TransDate of right(CONVERT(VARCHAR(10), TheDate, 103),7), which is stored as 'MM/YYYY'. Why this is I'm still not sure; however, to change it now would seem to be more work than I'd expected and having to reload many rows of data.
So, is there a way to convert this to 'mm/dd/yyyy'? I've tried using this and other methods, but still get conversion errors because of the mm/yyyy varchar format: cast(right(CONVERT(VARCHAR(10), TransDate, 103),7) as datetime)
Thanks in advance!
April 9, 2013 at 10:12 am
rarascon (4/9/2013)
I've been searching for something similar, but haven't quite found it. My dilemma is that this table was created with a TransDate of right(CONVERT(VARCHAR(10), TheDate, 103),7), which is stored as 'MM/YYYY'. Why this is I'm still not sure; however, to change it now would seem to be more work than I'd expected and having to reload many rows of data.So, is there a way to convert this to 'mm/dd/yyyy'? I've tried using this and other methods, but still get conversion errors because of the mm/yyyy varchar format: cast(right(CONVERT(VARCHAR(10), TransDate, 103),7) as datetime)
Thanks in advance!
Exactly what are you trying to accomplish?
April 9, 2013 at 10:16 am
rarascon (4/9/2013)
I've been searching for something similar, but haven't quite found it. My dilemma is that this table was created with a TransDate of right(CONVERT(VARCHAR(10), TheDate, 103),7), which is stored as 'MM/YYYY'. Why this is I'm still not sure; however, to change it now would seem to be more work than I'd expected and having to reload many rows of data.So, is there a way to convert this to 'mm/dd/yyyy'? I've tried using this and other methods, but still get conversion errors because of the mm/yyyy varchar format: cast(right(CONVERT(VARCHAR(10), TransDate, 103),7) as datetime)
Thanks in advance!
So when the row was created it took the current date and converted to text and only stored Month and Year? There is nothing you can do. Your system stored text not a date. This is reason #12939034785034057405667384 to ALWAYS store datatime information in a datetime column.
_______________________________________________________________
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/
April 9, 2013 at 10:22 am
I'm putting together a package to truncate and load this table each month, and I'm getting an error when using a parameter ? for the date. I'm using one that was already setup for date, so maybe as the other reply noted I should just setup a parameter with the varchar format. Not sure if that will work yet, because the parameter is still setup to look for dates. hmmmm...
April 9, 2013 at 10:24 am
I think you might be right, Sean. As I mentioned in the other reply to Lynn, I'm trying to execute SQL in a package that uses a parameter for date. I may bite the bullet and change this table to a date instead of varchar.
April 9, 2013 at 10:33 am
STUFF(TheDate, 3, 0, '/01')
Would convert 'mm/yyyy' to 'mm/01/yyyy'. You could then use it as a date, either implicitly (somewhat risky) or explicitly, 100% safe but you have to use CONVERT codes.
CONVERT(datetime, STUFF(TheDate, 3, 0, '/01'), 101)
You could also convert it to yyyymmdd, which would never require a conversion code:
RIGHT(TheDate, 4) + LEFT(TheDate, 2) + '01'
For example:
SELECT CAST(RIGHT(TheDate, 4) + LEFT(TheDate, 2) + '01' AS datetime)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2013 at 10:45 am
hm - thx Scott! Let me give this a shot!
April 9, 2013 at 10:48 am
yep, that did it! Saves me from having to redo the table and the related queries and inserts. thx again!
April 9, 2013 at 11:25 am
One more thing, I did wind up having to add a new column, because as Sean mentioned the original column as varchar is always going to be a thorn in my side. However, this gave me an easy option when adding the new column and populating the data by converting the dates from the original column.
April 9, 2013 at 12:52 pm
rarascon (4/9/2013)
One more thing, I did wind up having to add a new column, because as Sean mentioned the original column as varchar is always going to be a thorn in my side. However, this gave me an easy option when adding the new column and populating the data by converting the dates from the original column.
Nice!
A computed column is also an option:
ALTER TABLE dbo.tablename
ADD new_date AS CAST(...<TheDate>) AS date[time]
The column does not even have to be physically stored, and SQL will materialize it when needed. And you can use it like any "real" column, in a WHERE clause, ORDER BY, etc..
Btw, I too live where sometimes the real world doesn't match theory, and people actually do things that aren't theoretically correct (gasp!). Sometimes you just have to accept what is and deal with it :-).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2013 at 3:21 pm
I had encountered something similar a while ago in one of the assignments I had been a part of. I think the DB may have been originally migrated from MS Access, which has one of the data types that can store mm/yyyy. Based on specific needs, the application may not need the date (but just month and year).
- Rex
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply