Home Forums SQL Server 2008 SQL Server Newbies how to insert datetime column into table RE: how to insert datetime column into table

  • March 26, 2013 at 4:05 am

    #1600688

    denis.gendera (3/20/2013)

    All,

    I'm having strange issue trying to do the following which I thought was just going to be straight forward

    select top 5 machine.name, machine.model, machine.scantime

    'INSERT INTO [SMS_000].[dbo].[BMCMachines] ([ComputerName],[MachineModel],[stime]) VALUES (' + '''' + machine.name + ''',' + '''' + machine.model + ''',' + convert(datetime,machine.scantime,112) + ''')' from machine

    when I run the query comes back with following error

    Msg 241, Level 16, State 1, Line 1

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

    I can confirm that machine.scantime is "DateTime" column.

    The issue with the convert is that the 3rd arguement is the style and is used to set the style when you convert from a datetime to a varchar, not the other way around.

    What is the datatype of the [BMCMachines].[stime] column because the other issue is that the convert style of 112 is for an ISO date with no time element to it??