Solved

Inserting a 000000 date value in a field

Posted on 2011-02-22
2
668 Views
Last Modified: 2012-05-11
Hello,

I have an ascii file that i am inserting the records into a MS SQL (2008) table.  The Birthdate field is sometimes a valid date in mmddyy format which I am using Substring to put it into yymmdd format for the field in SQL.  The problem is when the date field in the ascii file is all zeros 000000 (mmddyy) Insert script will not run, I get an error saying "Conversion failed when converting date adn/or tiem from character string."  How can I get the ascii date value of 000000 accepted into the SQL date field?  The field in SQL is a date field and allows nulls?

Thanks,
0
Comment
Question by:JohnJMA
[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
2 Comments
 
LVL 8

Accepted Solution

by:
rushShah earned 500 total points
ID: 34953930
you can use case when like this,

insert into tablename
select case when date='000000' then null else date end as date
from tablename
0
 

Author Closing Comment

by:JohnJMA
ID: 34955385
Thanks, it works great.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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