Converting varchar dates to datetime
-
Is there a real need to convert dates that are stored in varchar format to datetime when using them in queries?
declare @testdate varchar(50)
set @testdate = '05/10/2004'
select * from staff where staff_date = @testdate
works the same as
select * from staff where staff_date = cast(@testdate as datetime)
staff_date column is defined as datetime. Both queries seem to work. Should I still cast it?
Also something like this seems to work
declare @testdate varchar(50)
set @testdate = (select TOP 1 staff_date from staff )
and again
select * from staff where staff_date = @testdate
works the same as
select * from staff where staff_date = cast(@testdate as datetime)
-
as long as you are using some predefined date format from TSQL you couldn't cast it.
But if you are not sure about the format cast it or better convert it. Because with the convert you can exactly control the date (and time) format of your input
Bye
Gabor -
...predefined sure..., but things can go wrong when differents languages are used by different users with the same query (or proc) as the implicite conversion between datetime and varchar datatypes will occurs based on the user's language...ex : french '27/08/2004' in a varchar converts fine with frenche language (dateformat 'dd/mm/yyyy') but fail with english language (dateformat 'mm/dd/yyyy')
-
Look I'm in Hungary and we use the Hungarian format (yyyy.mm.dd) and I was running some French companies as well.
SQLServer has really some predefied format where you don't have to think about, it will work. I'm personnaly using this predefined format (yyyymmdd). That one is the shortest and the most obvieus for me. You know I have a lot of difficulties to understand the dates like 05/10/2004. Is it mm/dd/yyyy or dd/mm/yyyy format?
I as a French personnaly would understand mm/dd/yyyy.
Bye
Gabor -
It depends on what you are going to do with the date/time value.
1. Just display it. Then VARCHAR is good enough and you don't have to CAST or CONVERT it to DATETIME.
2. Do calculations or other date functions (DATEADD, DATEDIFF, DATEPART, etc). The date/time MUST be in DATETIME datatype for those functions to work correctly.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply