?
Solved

SQL 2005 datetime datatype

Posted on 2006-07-21
11
Medium Priority
?
412 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

777 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