[dba-Tech] Quick SQL Question

[Martin Reid]

The convert is used elsewhere to actually create the view data. The query results windows in SQL server simply shows the date no time. For four records we are seeing the time portion when we run our code to get the data from the view. It's a real pain as this is code to do record matching on fields including DOB!

Its weird!!

Eg

DOB Match:
got a match on email= email address and empl -xxxxxxxxxxxx and dob - 03/03/1990
got a match on email= email address and empl - xxxxxxxxxxxx and dob - 10/01/1997

DOB NON-Match:
got a match on email= email address and empl - xxxxxxxxxxxx but NOT on dob which is 09/05/1994 00:00:00
got a match on email= email address and empl - xxxxxxxxxxxx but NOT on dob which is 14/04/1997 00:00:00

Martin

-----Original Message-----
From: dba-Tech <dba-tech-bounces at databaseadvisors.com> On Behalf Of Paul Hartland via dba-Tech
Sent: 26 July 2018 16:07
To: Discussion of Hardware and Software issues <dba-tech at databaseadvisors.com>
Cc: Paul Hartland <paul.hartland at googlemail.com>
Subject: Re: [dba-Tech] Quick SQL Question

hhmm thats a weird one been using sql for many years and converting dates in a similar manor and never had a problem, I am assuming that the years in the date were a typo (2001 and 2011)....where is the
CONVERT(varchar(10),ta.BIRTHDATE,103) is this in the view that is sometimes returning the time portion as well ?

have you tried comparing the results your getting with ta.BIRTHDATE  i.e.
select  CONVERT(varchar(10),ta.BIRTHDATE,103) as Converted_DOB, ta.BIRTHDATE as DOB and see what you get  in the DOB when a time portion shows in the  Converted_DOB ?

Paul

On 26 July 2018 at 15:57, Martin Reid <mwp.reid at qub.ac.uk> wrote:

> We have a view created for us in SQL Server. Field of interest is 
> DateOfBirth
>
> In the query interface it si showing as 21/1/2001
>
> However, for some records when we are getting the data from the view 
> there is a time stamp 21/1/2011 00:00:00
>
> The person creating the view is using
>
> CONVERT(varchar(10),ta.BIRTHDATE,103)
>
> The majority of records are fine its just the odd one or two that 
> return the time portion with the date of borth?
>
> Martin
>
>
>
>
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
>



--
Paul Hartland
paul.hartland at googlemail.com
_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com