sql - Casting varchar as date -

-

i think i've read every thread on topic , none of them solving problem.

first of all, database i'm working has date fields set varchar data type, drives me insane because have cast or convert whenever i'm working queries involving dates.

the query i'm working @ moment supposed pull list of medical patients have diagnosis, diagnosis has have been given before date. here's i've got.

select distinct     pd.patientid,     pd.patientname,     pmh.dateofonset     pmh.diagnosiscode patientdemographic pd join patientmedicalhistory pmh on pd.patientid = pmh.patientid pmh.diagnosiscode = '401.1'     , cast(pmh.dateofonset date) > cast('12/31/2014' date)

i'm getting error says "conversion failed when converting date and/or time character string." it's telling me error on line 1 though (the select distinct line) that's not helpful , i'm not sure need fix.

as mentioned, dateofonset field has varchar data type, , need find of dates came before end of december, 2014. i've tried remedy error trying different combinations of cast statements -- tried including cast on date field in select statement, , i'm still getting same error.

i working on query earlier required me find patient appointments within time frame, , query, had where clause set like:

where cast(visitdate date) between cast('01/01/2014' date) , cast('12/01/2014' date)

...and worked fine. since i've got current query set virtually same way, i'm not sure why i'm getting conversion error.

you have wrong dateformat:

set dateformat dmy; select cast('12/31/2014' date); --conversion failed when converting date and/or time character string.

you set mdy before executing query.

set dateformat mdy; select cast('12/31/2014' date);

livedemo

or use convert style 101:

set dateformat dmy; select convert(date,'12/31/2014',101)

if need store date varchar use @ least culture independent type iso-8601.

set dateformat dmy; select cast('20140201' date); -- 01.02.2014  set dateformat mdy; select cast('20140201' date) -- 01.02.2014

Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more