Solved

SQL 2005 datetime datatype

Posted on 2006-07-21
11
405 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now