Solved

SQL 2005 datetime datatype

Posted on 2006-07-21
11
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

689 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