Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jfm
Posting Yak Master
145 Posts |
Posted - 2013-05-02 : 05:05:23
|
Hi there, I have the following error: msg 241, level 16, state 1Conversion failed when converting date and/or time from character string Select CAST (S as varchar (1) ) as S ,S ,Sg ,Cd ,Sa ,Gen ,Id , cast(DATEDIFF(month,Gen,Sg) as varchar (4)) as PP from table_aThe data type of Gen is varchar (7) and Sg as well. Im using this same query in another table with the same data type and works. In this case I just need to subtract from Gen the value of Sg, both values are represented as follow: 10-2012, but the date type is not date is varchar.Any tip? Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-02 : 05:33:38
|
If you don't have permissions to alter table structure... --Just check the following illustration:SET DATEFORMAT DMYGODECLARE @Gen varchar (7) = '10-2012', @Sg varchar(7) = '12-2012'SELECT cast(DATEDIFF(month,CAST ('01-'+@Gen AS DATE),CAST('01-'+@Sg AS DATE)) AS VARCHAR)as PPNOTE: Better to change data types of both columns to DATE type--Chandu |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-05-02 : 06:28:09
|
Thanks a lot guys. Its working.quote: Originally posted by bandi If you don't have permissions to alter table structure... --Just check the following illustration:SET DATEFORMAT DMYGODECLARE @Gen varchar (7) = '10-2012', @Sg varchar(7) = '12-2012'SELECT cast(DATEDIFF(month,CAST ('01-'+@Gen AS DATE),CAST('01-'+@Sg AS DATE)) AS VARCHAR)as PPNOTE: Better to change data types of both columns to DATE type--Chandu
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-02 : 06:33:22
|
quote: Originally posted by jfm Thanks a lot guys. Its working.quote: Originally posted by bandiNOTE: Better to change data types of both columns to DATE type
Try to follow ISO Date Format with date time types and use DATE/DATETIME data types for date values...Welcome--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-02 : 07:04:56
|
quote: Originally posted by bandi
quote: Originally posted by jfm Thanks a lot guys. Its working.quote: Originally posted by bandiNOTE: Better to change data types of both columns to DATE type
Try to follow ISO Date Format with date time types and use DATE/DATETIME data types for date values...Welcome--Chandu
which is what i've explained in link above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|