Conversion failed when converting date and/or time from character string - but no idea why

Question

I have the following table:

USE [junglegymSQL]
GO

ALTER TABLE [dbo].[UserSession] DROP CONSTRAINT [FK_UserSession_UserID]
GO

ALTER TABLE [dbo].[UserSession] DROP CONSTRAINT [DF__UserSessi__Creat__5CA1C101]
GO

ALTER TABLE [dbo].[UserSession] DROP CONSTRAINT [DF__UserSessi__Creat__5BAD9CC8]
GO

/****** Object:  Table [dbo].[UserSession]    Script Date: 16/09/2013 3:44:55 PM ******/
DROP TABLE [dbo].[UserSession]
GO

/****** Object:  Table [dbo].[UserSession]    Script Date: 16/09/2013 3:44:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserSession](
    [UserId] [int] NOT NULL,
    [SessionId] [varchar](500) NOT NULL,
    [Created] [datetime] NOT NULL,
    [CreatedBy] [varchar](50) NOT NULL,
    [LastModifed] [datetime] NULL,
    [LastModifiedBy] [varchar](50) NULL,
 CONSTRAINT [PK_SessionID] PRIMARY KEY CLUSTERED 
(
    [SessionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[UserSession] ADD  DEFAULT (user_name()) FOR [Created]
GO

ALTER TABLE [dbo].[UserSession] ADD  DEFAULT (getdate()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[UserSession]  WITH CHECK ADD  CONSTRAINT [FK_UserSession_UserID] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO

ALTER TABLE [dbo].[UserSession] CHECK CONSTRAINT [FK_UserSession_UserID]
GO

Which I'm trying to write to using the following PHP:

$sessiontoken = dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647));

// Save our cookie 'qcore' with the users session id
setcookie("qcore", $sessiontoken);

$query = "
    INSERT INTO dbo.UserSession ( 
        UserId ,
        SessionId
    ) VALUES  (
        :userid ,
        :sessionid  
    )
";

$query_params = array( 
    ':userid' => intval($row['UserId']), 
    ':sessionid' => $sessiontoken 
); 

try 
{ 
    // Execute the query to create the user 
    $stmt = $db->prepare($query); 
    $result = $stmt->execute($query_params); 
} 
catch(PDOException $ex) 
{ 
    // Note: On a production website, you should not output $ex->getMessage(). 
    // It may provide an attacker with helpful information about your code.  
    // die("Failed to run query: " . $ex->getMessage()); 
    die("Failed to run query: " . $ex->getMessage()); 
}

However each time I do this, I receive the following error:

Failed to run query: SQLSTATE[22007]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string.

I've checked, and the following line is returning an integer:

':userid' => intval($row['UserId']),

and given that my second column is a varchar, well, I'm confused. Why am I seeing this error when I'm not working with dates at all and I'm passing an integer value?

bummi · Accepted Answer · 2013-09-16 06:00:48Z

2

Your column Created is of type datetime and you are using a Varchar as default.

ALTER TABLE [dbo].[UserSession] ADD  DEFAULT (user_name()) FOR [Created]

looks like you reversed Created and CreatedBy.

answered Sep 16, 2013 at 6:00

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vaibs_Cool · Accepted Answer · 2013-09-16 05:57:35Z

0

Have you specified the date format ??

Select CONVERT(Date, '17-9-2013', 105)

105 stands for (dd-mm-yyyy).

Please refer http://msdn.microsoft.com/en-us/library/ms187928.aspx

answered Sep 16, 2013 at 5:57

Vaibs_Cool

6,1506 gold badges30 silver badges61 bronze badges

Collectives™ on Stack Overflow

Conversion failed when converting date and/or time from character string - but no idea why

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related