Solved

SQL Convert Date including Zeros

Posted on 2004-03-22
11
7,693 Views
Last Modified: 2013-11-18
Hello,

I have this convert date code:

      CONVERT(VARCHAR(15), Date, 101) As Date

ok that's good it formats the date to:  3/22/2004

~~BUT~~

In SQL Server 2000, I have some dates that have zeros like 03/22/2004 also 3/02/2004

When I run something like:

Select CONVERT(VARCHAR(15), Date, 101) As Date FROM MyTable WHERE Date BETWEEN '3/1/2004' AND '3/31/2004'

The values:

    3/01/2004, 3/05/2004, 3/09/2004 etc. are not included in my query.

Only the range of:
3/10/2004 to 3/30/2004 are shown (so the 0's in the day are excluded)

Is there a way to include BOTH     03/04/2004   as well as 3/4/2004 (as an example)?

Thanks!

Chris

0
Comment
Question by:Trancedified
[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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 10654686
Hi Trancedified,
are you converting the column to a date field in the where clause also?
0
 
LVL 1

Author Comment

by:Trancedified
ID: 10659392
Iwadwell,

I just tried the same CONVERT line in the WHERE clause and it does the same thing.

Any other ideas?

Chris

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 10661687
my immediate thought was that you are comparing character strings - and not dates as you should be.  
This is why I believe dates with a leading 0 are ignored, as '0xxxx' is not between '3xxxx' and '3xxxx'.

Are you sure that the '3/1/2004' AND '3/31/2004' values are being treated as dates?  
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Expert Comment

by:nmwis70
ID: 10662679
Try "WHERE Date > '3/1/2004' AND DATE < '3/31/2004'"
0
 
LVL 1

Author Comment

by:Trancedified
ID: 10670961
Iwadwell,

Yeah you're right unfortunately "Date" has a data type in SQL Server as "nvarchar" (character string) I wish it was datetime. That's why I am hoping under my SELECT clause:

    Select CONVERT(VARCHAR(15), Date, 101) As Date  

it would change the character string into DateTime??? with the style of 3/24/2004
Would you happen to know another CONVERT clause so that nvarchar would mimic datetime & my BETWEEN clause would be treated as a REAL date?

~~~nmwis70~~~,

I tried:

    "WHERE Date > '3/1/2004' AND DATE < '3/31/2004'"

But there's a syntax error at ">" I also tried:

    "WHERE Date > '3/1/2004' AND < '3/31/2004'"

I assume the only acceptable syntax would be BETWEEN  (SQL, but written in Visual Basic.NET)
Thanks both of you for replying, got anymore ideas?

Points are increased +100 to 500

Chris
0
 
LVL 4

Expert Comment

by:nmwis70
ID: 10671460
This is working for me, only thing I changed was Datetime... maybe try that?

Select CONVERT(VARCHAR(15), DateColumn, 101) As Datetime
FROM MyTable
WHERE CONVERT(VARCHAR(15), DateColumn, 101) As Datetime BETWEEN '3/1/2004' AND '3/31/2004'

Good luck.
0
 
LVL 4

Accepted Solution

by:
nmwis70 earned 500 total points
ID: 10671511
Whoops,... ignore that previous post, use this query instead:


Select CAST(DateColumn AS datetime) As DateColumn
FROM MyTable
WHERE CAST(DateColumn AS datetime) BETWEEN '3/1/2004' AND '3/31/2004'

0
 
LVL 1

Author Comment

by:Trancedified
ID: 10672802
nmwis70,

VERY close that works for 2 of my tables where the date is nvarchar..... BUT one of my other tables has date as a regular datetime.... VERY strange but when I export it to a .xml file that's read in Excel 2003, I get:

     2001-01-05T00:00:00.0000000-08:00  

That is why I needed:
   
    Select CONVERT(VARCHAR(15), DateColumn, 101) As DateColumn

Hopefully the last question: is there a way to combine your CAST along with my CONVERT to accommodate fields w/ datetime AND nvarchar as data types?

CONVERT((VARCHAR(15), DateColumn, 101) CAST(DateColumn AS datetime) AS DateColumn))

I'm trying something similiar to that now...

Chris

0
 
LVL 4

Expert Comment

by:nmwis70
ID: 10677175
It may be too much to ask 1 query to perform the same specific operation on 2 different datatypes.  Can you have 2 seperate queries perform the operations you need?  Or does your task require that the operation occur at the same time?

If you can't do the operation across two different queries you can use the UNION command to merge the results of two queries.  The first query can perform the SELECT that worked for 2 of your tables, and the second SELECT can perform the regular DATE comparison which you know how to do, i.e...

Select CAST(DateColumn AS datetime) As DateColumn
FROM MyTable
WHERE CAST(DateColumn AS datetime) BETWEEN '3/1/2004' AND '3/31/2004'
        UNION
Select CAST(DateColumn AS datetime) As DateColumn
FROM MyTable
WHERE CAST(DateColumn AS datetime) BETWEEN '4/1/2004' AND '4/31/2004'

Here is a quick tutorial about the UNION command at w3c.  http://www.w3schools.com/sql/sql_union.asp.  I think were getting close.
0
 
LVL 1

Author Comment

by:Trancedified
ID: 10689049
nmwis70,

Thanks for the info on UNIONs, my boss came to me and told me all dates will be in datetime format not nvarchar's or varchar's anymore, but i ran into a weird problem... the BETWEENs won't work even with a datetime datatype BECAUSE SQL Server somehow stores a hidden time frame T00:00:00.0000000-08:00 that you can't see in the table.

Not to worry I experimented and combined both your code and mine together, so in the SELECT clause I convert it to VARCHAR with only 10 characters so that the time is sliced off.

In the WHERE..... BETWEEN clause I used your code to convert it back to datetime but it retains the 10 character rule. Then it works and I'm able to select a date range w/ BETWEEN as well as when I export the information to a .XML and open it in Excel, it will retain the date as

(today)  "3/26/2004" NOT 2004-03-26T00:00:00.0000000-08:00  

Here is the simplified statement:

SELECT CONVERT(VARCHAR(10), Date, 101) AS Date
FROM TD4
WHERE CAST(CAST(Date AS datetime) AS datetime)                       <--- (could work as just "WHERE CAST(Date AS datetime) BETWEEN.....)"
BETWEEN '3/1/2004' AND '3/31/2004'
                       
Excellent job, nmwis70!

Chris
0
 

Expert Comment

by:Mandar_Ghalsasi
ID: 10865886
How to find fiscal week of the year in oracle
0

Featured Post

Myth Busting: MongoDB Scalability (it scales!)

I was talking with one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. He mentioned to me that several customers have been telling him that “MongoDB doesn’t scale!” MongoDB’s scalability was in question?

My response was, “Is that a joke?"

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

628 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