Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Declaring date field as varchar

Posted on 2007-10-18
6
Medium Priority
?
1,052 Views
Last Modified: 2011-10-03
My coworker wrote a script and he declared the date field as varchar(10). When i run it using datetime and varchar..i get the same results back.

It's ok to declare datetime field as varchar??

Script is simple like this:
declare @RunDate as varchar(10)
set @rRunDate = '2007-10-18'

select  * from  table
where datefield >= @RunDate
       and  anotherfield ='H'
0
Comment
Question by:Camillia
6 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 400 total points
ID: 20104529
It is ok as SQL will implicitly convert any string data that looks like a real date into a date.  If I was always putting date type data into that variable though I'd declare it as a smalldatetime or datetime, as appropriate.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 800 total points
ID: 20104541
>It's ok to declare datetime field as varchar??
Not really, unless you knows when an implicit casting will be performed by SQL server

In this case 'datefield >= @RunDate'; since DateField is of Datetime datatype, @Rundate is  implicitly  casted to datetime.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 20104549
>It's ok to declare datetime field as varchar??
it should be avoided to do like that.

correct, with implicit conversion:

declare @RunDate as datetime
set @rRunDate = convert(datetime, '2007-10-18', 120)

the format YYYY-MM-DD (which is the style=120 in the above code) is non-ambigeous, hence it should work on all servers without any problems.
it is however a good habit to always use the explicit data type conversion anyhow..

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 7

Author Comment

by:Camillia
ID: 20108509
>> since DateField is of Datetime datatype, @Rundate is  implicitly  casted to datetime.
 ok, that makes sense why the script works.

Now, angellll has this :
declare @RunDate as datetime
set @rRunDate = convert(datetime, '2007-10-18', 120)

My coworker has declare @RunDate as varchar(10). Should we change the script to do a conversion before using the it..like below? (for some reason, he has to declare it as varchar..otherwise the vendor app wont work)....
declare @RunDate as varchar(10)  --have varchar here
set @rRunDate = convert(datetime, '2007-10-18', 120)   --convert to datetime before using it..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20108637
>(for some reason, he has to declare it as varchar..otherwise the vendor app wont work)....
can you explain that?

now:
>declare @RunDate as varchar(10)  --have varchar here
>set @rRunDate = convert(datetime, '2007-10-18', 120)   --convert to datetime before using it..

will be a bad idea, because that will implicitely make the datetime value that has been carefully constructed with the convert() statement back into a varchar, with the current date format.
which could be dd mon yyyy, and hence it would cut of the last digit of the year...

0
 
LVL 7

Author Comment

by:Camillia
ID: 20108859
He couldnt pass datetime from the vendor app to SQL because "they have different delimiters for the time field".

Our vendor has defined their own user-defined-type. It's a datetime, length 8. They named it  *ISODate.

Going to ask him to try that and see if it would work with the vendor app.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 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