We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


SQL Convert Date including Zeros

Medium Priority
Last Modified: 2013-11-18

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


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)?



Watch Question

Lee WadwellProject Architect

Hi Trancedified,
are you converting the column to a date field in the where clause also?



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

Any other ideas?


Lee WadwellProject Architect

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?  

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



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?


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


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.
Unlock this solution and get a sample of our free trial.
(No credit card required)



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:


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...


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'
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.



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:

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!

How to find fiscal week of the year in oracle
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.