Solved

SQL Convert Date including Zeros

Posted on 2004-03-22
11
7,682 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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