Conversion error for date

[corbinap]

I am getting an error:
Conversion failed when converting the nvarchar value '10/2007' to data type int.

using:

a.[calyearmonth] = b.[calyearmonth] - 1

calyearmonth is a nvarchar and I need to get previous month in table b.

Do I need to covert calyearmonth somehow?

 

[ptheriault]

You can't subtract 1 from '10/2007'. If you're trying to subtract 1 month then select the month part only using substring and convert it to an int. Then subtract 1.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[genomon]

Or check into the DateDiff function...

Always remember that you're unique. Just like everyone else.

 

[gmmastros]

>> Do I need to covert calyearmonth somehow?

Yes.

The problem here is that you cannot simply subtract 1. That is a math operation, and you are using an nvarchar. What's worse is that you could end up with faulty data. For example, what would happen if calmonthyear = '01/2008' Subtracting 1 should result in '12/2007'. So, to get the correct result, you need to convert this to a proper date, subtract 1 month, and then convert it back to your month/year format.

Let's do this in steps:

First, convert to a date. Since we don't care what the day is, we can simply use the first day of the month. so...

[!]'1/' + [/!]calymonthyear = '1/10/2007'

This is a well known date format widely used in europe. So, we can convert this to a proper datetime data type like this...
[!]Convert(DateTime,[/!] '1/' + CalYearMonth[!], 103)[/!]

The 103 (3rd parameter) tells SQL Server to use dd/mm/yyyy as the format.

Now that this is a proper date, we can subtract 1 month from it (using the DateAdd function).

[!]DateAdd(Month, -1, [/!]Convert(DateTime, '1/' + CalYearMonth, 103)[!])[/!]

We are adding a -1 month to the date, which will give us the first day of the previous month.

Now, we need to convert this back to your format MM/YYYY. There is no built in format for this, so we will need to do it in steps. First, convert it to a string while applying a dateformat of dd/mm/yyyy.

[!]Convert(VarChar(10),[/!] DateAdd(Month, -1, Convert(DateTime, '1/' + CalYearMonth, 103))[!], 103)[/!]

Now, we need to remove the first 3 characters, or, actually, return the last 7, so....

[!]Right([/!]Convert(VarChar(10), DateAdd(Month, -1, Convert(DateTime, '1/' + CalYearMonth, 103)), 103)[!], 7)[/!]

Make sense?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AlexCuse]

This is going to be EXTREMELY ugly. I suggest you rethink the design of this table. But here is an example. This will be very rigid and ONLY work to subtract one month. I suggest you just store your yearmonth nonsense as a real date, like '2006-06-06' or something.

[COLOR=green]--sample data
[/color][COLOR=blue]declare[/color] @calyearmonth [COLOR=blue]table[/color] (crp [COLOR=blue]varchar[/color](666))

[COLOR=blue]insert[/color] @calyearmonth
[COLOR=blue]select[/color] [COLOR=red]'10/2007'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'12/2007'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'01/2007'[/color]

[COLOR=green]--test query
[/color][COLOR=blue]select[/color] crp 
[COLOR=green]--this is what you have to do, just to subtract one month 
[/color][COLOR=green]--if you stick with this garbage design
[/color][COLOR=green]--if you store as a real date (2007-10-10 or whatever) you could just use dateadd funtion
[/color], [COLOR=blue]case[/color] [COLOR=#FF00FF]left[/color](crp, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], crp) -1)
[COLOR=blue]when[/color] [COLOR=red]'01'[/color] [COLOR=blue]then[/color] [COLOR=red]'12/'[/color] + [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]right[/color](crp, 4) [COLOR=blue]as[/color] [COLOR=blue]int[/color]) - 1 [COLOR=blue]as[/color] [COLOR=blue]char[/color](4))
[COLOR=blue]else[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]left[/color](crp, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color], crp) - 1) [COLOR=blue]as[/color] [COLOR=blue]int[/color]) -1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](2)) + [COLOR=#FF00FF]right[/color](crp, 5)
[COLOR=blue]end[/color] [COLOR=blue]as[/color] minusonemonth

[COLOR=blue]from[/color] @calyearmonth

Hope this helps,

Alex


[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[corbinap]

Thanks for all of the suggestions.

 

[ESquared]

Store your dates as smalldatetime or datetime, with the day part being a 1, like 2007/10/1.

If you need to select a specific month you just do it with the 1: WHERE datecol = '2007/10/1'.

If you need a date range, you can do it now:

WHERE datecol BETWEEN '2007/10/1' and '2007/12/1' -- inclusive of december

and you can do math on it with the date functions in SQL server such as

UPDATE table SET datecol = DateAdd(mm, -1, datecol) WHERE <condition>

Continuing to use a char datatype to store dates is Not A Good Idea.

If you are stuck because some legacy application is using that column, then you could look into computed columns, or views, so that the legacy application gets what it expects but the data is REALLY stored as datetime.

If the legacy application is only doing SELECTs and no INSERTs or UPDATEs then you could do something like this:

alter table yourtable add realdatecol datetime
update yourtable set realdatecol = Convert(datetime, '1/' + datecol, 103) -- use dd/mm/yyyy order
alter table yourtable drop column datecol
alter table yourtable add datecol as Substring(Convert(varchar(10), realdatecol, 103), 4, 7)

or to support updates and inserts, you could do the above but avoid the computed column and keep the column name datecol, then create a view named the old table name that returns all columns from the table except it converts the datecol to the desired text, and has an instead of update/insert trigger that converts the text datecol value to a datetime one and does the update or insert.