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

Declaring date field as varchar

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
Camillia
Asked:
Camillia
3 Solutions
 
Chris MangusDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
CamilliaAuthor Commented:
>> 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>(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
 
CamilliaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now