• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7716
  • Last Modified:

SQL Convert Date including Zeros

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
Trancedified
Asked:
Trancedified
  • 4
  • 4
  • 2
  • +1
1 Solution
 
lwadwellCommented:
Hi Trancedified,
are you converting the column to a date field in the where clause also?
0
 
TrancedifiedAuthor Commented:
Iwadwell,

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

Any other ideas?

Chris

0
 
lwadwellCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nmwis70Commented:
Try "WHERE Date > '3/1/2004' AND DATE < '3/31/2004'"
0
 
TrancedifiedAuthor Commented:
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
 
nmwis70Commented:
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
 
nmwis70Commented:
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
 
TrancedifiedAuthor Commented:
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
 
nmwis70Commented:
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
 
TrancedifiedAuthor Commented:
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
 
Mandar_GhalsasiCommented:
How to find fiscal week of the year in oracle
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now