Solved

SQL 2005 datetime datatype

Posted on 2006-07-21
11
408 Views
Last Modified: 2012-06-21
I have a field with a datatype of datetime called test.  The value I'm trying to insert is 07/21/2006.  The insert statement executes without error, however, the value that is entered into the DB is not correct.  This is the value that gets entered: 1900-01-02 00:00:00.000.  Below is my insert statement.  Any clue what I'm doing wrong?

insert into tbl_Journal(fgn_folioRecId, txtActivity, txtNote, fgn_userRecId, bitFollowUp, test, intCreatedBy)
values(66, 'Comment to county', 'sdf sdfa fds daf', 24, 07/21/2006, 1, 24)
0
Comment
Question by:lostboyinsofla
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17154384
insert into tbl_Journal(fgn_folioRecId, txtActivity, txtNote, fgn_userRecId, bitFollowUp, test, intCreatedBy)
values(66, 'Comment to county', 'sdf sdfa fds daf', 24, CONVERT(DATETIME, '07/21/2006', 101), 1, 24)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17154445
set dateformat mdy

insert into tbl_Journal(fgn_folioRecId, txtActivity, txtNote, fgn_userRecId, bitFollowUp, test, intCreatedBy)
values(66, 'Comment to county', 'sdf sdfa fds daf', 24, '07/21/2006', 1, 24)
0
 
LVL 12

Expert Comment

by:AshleyBryant
ID: 17154456
You could also just put quotes or pound signs around the date, I forget which is the accepted method.

insert into tbl_Journal(fgn_folioRecId, txtActivity, txtNote, fgn_userRecId, bitFollowUp, test, intCreatedBy)
values(66, 'Comment to county', 'sdf sdfa fds daf', 24, "07/21/2006", 1, 24)

OR

insert into tbl_Journal(fgn_folioRecId, txtActivity, txtNote, fgn_userRecId, bitFollowUp, test, intCreatedBy)
values(66, 'Comment to county', 'sdf sdfa fds daf', 24, #07/21/2006#, 1, 24)


The reason you are having a problem is that the sql serveris treating it as a methematical expression: 7 divided by 21 divided by 2006, instead of treating it as a date.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17154505
AshleyBryant,
>  7 divided by 21 divided by 2006,
This is true, When you try to insert a '0' into a datetime column, sql server will set it as '1/1/1900'
see this

declare  @i datetime
set @i =  0
select @i
0
 

Author Comment

by:lostboyinsofla
ID: 17154574
Mr_Peerapol,
I get this error when using your insert statement:

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to bit is not allowed. Use the CONVERT function to run this query.
0
 

Author Comment

by:lostboyinsofla
ID: 17154587
AshleyBryant,
I get this error when I run your insert statement.

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '07/21/2006' to data type bit.
0
 

Author Comment

by:lostboyinsofla
ID: 17154611
AshleyBryant,
When I try to use double quotes I get:
Msg 128, Level 15, State 1, Line 2
The name "07/21/2006" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


When I try to user pound signs I get:
Msg 128, Level 15, State 1, Line 2
The name "#07" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 17154613
i think  bitFollowUp  is a bit column

SET DATEFORMAT mDY
insert into tbl_Journal(fgn_folioRecId, txtActivity, txtNote, fgn_userRecId, bitFollowUp, test, intCreatedBy)
values(66, 'Comment to county', 'sdf sdfa fds daf', 24, 1, 24, '07/21/2006') --- change the order
0
 

Author Comment

by:lostboyinsofla
ID: 17154620
aneeshattingal,
I'm not sure what you're suggesting.
0
 

Author Comment

by:lostboyinsofla
ID: 17154637
aneeshattingal,

I'm such a dumb butt.  I guess sometimes it just takes a second pair of eyes LOL :)

Thank you
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17154639
You are trying to insert a date into bitFollowUp column. What's the datatype of bitFollowUp? Maybe it's BIT ?
In the insert statement, you might need to match the value being inserted to the correct columns.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question