CODEWITHSUNDEEP
sqlsql-server
user3580480
user3580480

Reputation: 482

Date format on INSERT statement

The following SELECT statement works and returns the date in the correct FORMAT.

SELECT 
' - '+c.[customercli] 
,c.[customerlookup]  
,c.[customername] 
,c.[linedescription] 
,c.[Sum of buy price] 
,c.[Sum of sell price]  
,c.[qty] 
,c.[Billingmonth] 
,FORMAT([FromDate],'dd/MM/yyyy') 
,FORMAT([ToDate],'dd/MM/yyyy') 

FROM [MasterBill].[dbo].[DaisyCallsCurrentBill] c

But when I run the following INSERT / SELECT statement, I get the following error.

"Conversion failed when converting date and/or time from character string."

INSERT INTO [MasterBill].[dbo].[CurrentBillMaster]
([identifier]
,[customer id]
,[customer name]
,[description]
,[buy price]
,[sell price]
,[qty]
,[Billingmonth]
,[FromDate]
,[ToDate]) 

SELECT 
' - '+c.[customercli] 
,c.[customerlookup]  
,c.[customername] 
,c.[linedescription] 
,c.[Sum of buy price] 
,c.[Sum of sell price]  
,c.[qty] 
,c.[Billingmonth] 
,FORMAT([FromDate],'dd/MM/yyyy') 
,FORMAT([ToDate],'dd/MM/yyyy') 

FROM [MasterBill].[dbo].[DaisyCallsCurrentBill] c

The FromDate and ToDate on both tables are configured with a type of "date"

Any help greatly appreciated.

UPDATE: the date is in the following format on the [dbo].[DaisyCallsCurrentBill] table 2014-09-01 - I simply want to change this to 01/09/2014

Upvotes: 1

Views: 2758

Answers (2)

Bhaskar
Bhaskar

Reputation: 1036

Here it looks like you are inserting date which is in string type. Remove the format function and it should work. Why? Formatting is just a way to represent the date. Database stores date in its own way, you cannot change it.

Upvotes: 2

Mureinik
Mureinik

Reputation: 310993

Since you're selecting a date field and attempting to insert it to another date field, formatting is meaningless, and shouldn't be used:

INSERT INTO [MasterBill].[dbo].[CurrentBillMaster]
([identifier]
,[customer id]
,[customer name]
,[description]
,[buy price]
,[sell price]
,[qty]
,[Billingmonth]
,[FromDate]
,[ToDate]) 

SELECT 
' - '+c.[customercli] 
,c.[customerlookup]  
,c.[customername] 
,c.[linedescription] 
,c.[Sum of buy price] 
,c.[Sum of sell price]  
,c.[qty] 
,c.[Billingmonth] 
,c.[FromDate], -- Note: no formatting
,c.[ToDate] -- here too

FROM [MasterBill].[dbo].[DaisyCallsCurrentBill] c

Upvotes: 0

Related Questions