Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2005 datetime datatype

Posted on 2006-07-21
11
Medium Priority
?
413 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

610 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