Solved

SQL Convert Date including Zeros

Posted on 2004-03-22
11
7,630 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
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 …

705 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now