Hi All,

I am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB

ColA
3/12/2014 12:00AM
3/19/2014 12:00AM
ColB
3/13/2014
3/20/2014


I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."

Convert (varchar(10), "StartDate", 110)+1 as Next Day

Any and all help is greatly appreciated. TIA

Use Dateadd function and define your column as Date. You are confused by date/datetime and int.

Select Cast(Dateadd(day,1,'3/12/2014 12:00AM') as date)

--  2014-03-13

• Proposed as answer by Naomi NModerator 10 hours 31 minutes ago

Thanks for the response. I plugged it in exactly as shown and received this message

Hi

Please check this article regarding the CONVERT part:
http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx

I recommend to read it all, but if you really dont have time you can jump to the Conclusions and Summarize. Basically you are using implicit style converting and you should use style during the convert, or chose another format which fit your specific configuration (I do not recommend the last option! but using implicit convert with style... check the article

Where exactly (in which tool) did you type that code? I just tried it in SSMS and it worked just fine. 

Using this code might work in one culture and might not work in another culture, as I explained above in short (and the article that I linked explained in details).

I highly recommend not to use the code as it is, even if it work for you today (the fix is very small and there is no reason that you limit yourself to specific culture).

please try to execute the code using "SET  LANGUAGE German;" for example and you will get error if the date is '3/19/2014 12:00AM' (this is the second example in the OP sample)

SET  LANGUAGE German;
select Cast(Dateadd(day,1,'3/19/2014 12:00AM') as date)
GO
-- ERROR: Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert auerhalb des gltigen Bereichs.

I Do not understand German but I can guess that the error is "out of range", since this query use explicit convert the string to datetime (using the Dateadd with a string create implicit convert!) and in German it try to use the number 19 as